How to expand data stored in list format

In Power BI, you can extract data from a column in a list and concatenate it into a text format.

List

Here is a guide for getting the data stored in the list:

  • Open your Power Dashboard in your PowerBI Desktop app
  • In the “Home” tap click “Transform data”
  • Go to the table (in this case the “Responses” table) with the column where data is stored as a list
  • Now add a Custom Column. We use this column to store the data from the list

Custom Column

Start by naming the Custom Column accordingly.

In the Custom column formula you add the following formula:

Text.Combine([ColumnToExtract], ", ")
Formula1

You need to change [ColumnToExtract] with the column where data in list format is stored. In this example we insert the column SurveyAnswersSimple.Q2-1.value.

Now the formula looks like this:

Text.Combine([#"SurveyAnswersSimple.Q2-1.value"], ", ")
Formula2

Click “OK” and “Close & Apply” the settings.

Now your data should be visible and ready for use:

Data