Expand list with records to columns

[a_hauser]

Hi community! 

I have to analyse some data out of a rest API query. Original source is JIRA. 
Here I'm faced with some issues while expanding a column with lists and each list could have more than 1 record. 

At the moment every record is written in a new row. But I would like to get a separate column for each record of the list for each unique ID. Furthermore the entries of the record have different types: text, date and boolean. In general i would need online one entry of the records for my analysis. 

No it looks like: 

ID_1	Record
ID_1	Record
ID_2	Record
ID_3	Record
ID_3	Record
ID_3	Record

But would I'd like to have is: 
only 1 row per ID, not depending on number of records behind.

ID_1	Record	Record
ID_2	Record
ID_3	Record	Record	Record

I hope it's understandable 🙂

[v-cgao-msft · Accepted solution]

Hi @a_hauser ,

Please refer to the following steps:

let
    Source = [ID={"ID_1","ID_1","ID_2","ID_3","ID_3","ID_3"},ColumnName={[a=11],[a=22],[a=33],[a=44],[a=55],[a=66]}],
    #"Converted to Table" = Record.ToTable(Source),
    #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"),
    #"Expanded ID" = Table.ExpandListColumn(#"Pivoted Column", "ID"),
    #"Expanded ColumnName" = Table.ExpandListColumn(#"Expanded ID", "ColumnName"),
    #"Grouped Rows" = Table.Group(#"Expanded ColumnName", {"ID"}, {{"data", each Table.Transpose(Table.SelectColumns(_,{"ColumnName"}))}}),
    #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"})
in
    #"Expanded data"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[MAwwad]

To expand a column with lists into separate columns in PowerBI, you can use the following steps:

1. Load the data into PowerBI: First, you need to load the data into PowerBI from the rest API query.

2. Go to the Power Query Editor: Once the data is loaded, go to the Power Query Editor to modify the data.

3. Expand the column: Right-click on the column you want to expand and select "Expand to new rows." In the "Expand" dialog box, select the columns you want to expand, and then click "OK."

4. Group the data: Go to the "Home" tab and select "Group By." In the "Group By" dialog box, select the unique ID column, and then click "OK."

5. Pivot the data: Right-click on the expanded column and select "Pivot Column." In the "Pivot Column" dialog box, select the options you want, and then click "OK."

6. Load the data: After you've completed the steps above, go back to PowerBI, and then select "Close & Apply" to load the data.

This will expand the column with lists into separate columns in PowerBI, and you'll have only one row per ID, regardless of the number of records behind.