Search code examples
powerbipowerquerytransformdata-cleaningm

PowerBI - How to pivot answer lists to column answers

In PowerBI, I have a data table which includes a question column and an associated answer column. In the answer column there are a mixture of lists (lists may contain one or more answers) and non-list values.

The question column should be pivoted so that each question is a new column with the associated answers from the answer column. There are also a number of other columns in the data table I want to keep in the output data table.

Starting example data table format

enter image description here

Desired example data table format

enter image description here

Please see the answer below for the workaround I used.

Solution

  • The query was resolved as follows:

    1/. Separate each question and answer combination in to their own dfs.

    2/. Expand the lists (where present) in each new df.

    3/. Pivot the fields in each df.

    4/. Merge the separate dfs in to a common df using a shared key column (in this example the 'Code' column) keeping the relevant columns in the new common df.