Search code examples
powerbipowerbi-desktop

Split a column of lists into multiple columns in PowerBI

I have imported a JSON file into PowerBI and it contains a column in which the values are of type "List". I am looking to expand that column into multiple columns.

Specifically, the data contains a Sprint Name, the start date and the end date of the sprint, along with some other values associated with each sprint.

Trying to use "Expand to new rows" duplicates each sprint instance, creating a table that looks like this, duplicating each sprint instance multiple times for each associated value:

Sprint Name       Value

JAN(S1Dev)     2019-01-01
JAN(S1Dev)     2019-01-13
JAN(S1Dev)     {attribute}
JAN(S1Dev)     {attribute}
JAN(S2Dev)     2019-01-14
JAN(S2Dev)     2019-01-31
JAN(S2Dev)     {attribute}
JAN(S2Dev)     {attribute}
FEB(S1Test)    2019-02-01
FEB(S1Test)    2019-02-15
...            ...

I would like to do something similar to the "expand" feature, which instead creates a new column with each attribute rather than a new row. This is currently vastly increasing the size of my table for no reason, while also making the data practically un-useable. Any help would be appreciated, cheers!

Solution

  • I have found a very simple solution to this, but as it took me some time to figure it out I will answer my own question instead of deleting it to help others in the future...

    Upon importing the JSON data into PowerBI first select "Convert to Table" to view the data as a table with editable properties.
    enter image description here

    Next, click the arrows pointing away from each other at the top of the column of Lists, and select "Extract Values".

    enter image description here

    Select a delimiter to use for concatenating values, I am choosing a comma since I know that the data contained within the list does not have any commas in it. If your data contains commas within it, choose something else. Similarly, if your data contains one of the delimiters, do not choose that as the delimiter. enter image description here It should now display a comma-separated list where it previously displayed "List" in orange text.

    Now, right-click on the column and select "Split Column" then choose "By Delimiter" enter image description here Select the delimiter that you previously chose, and under "split at" select "Each occurrence of the delimiter" then click OK.

    Your column should now be split into multiple columns based on the list!