cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SVUser123
Frequent Visitor

Convert cell list in Col1 to separate columns

Hi Team,

 

I am new to Power BI and stuck in converting my server data into tables.

I got a response from server as below 

 

------------

Column1

------------

List

List

List

List

 

I want to make new table with each of the these lists to seperate columns. 

The number of lists are dynamic per grid.

Please anyone help me with the pq code 

1 ACCEPTED SOLUTION

Hi @SVUser123 

I create a sample to have a test.

Sample:

1.png

My Code:

let
Source = #table({"1", "2","3"}, {{ {1,2}, {3,4},{4,5} }}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Value"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns", {{"Index", type text}}, "en-US")[Index]), "Index", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2","3"}, each List.Transform(List.Zip(_), each Record.FromList(_,{"1","2","3"})),"Merged"),
    #"Expanded Merged" = Table.ExpandListColumn(#"Merged Columns", "Merged"),
    #"Expanded Merged1" = Table.ExpandRecordColumn(#"Expanded Merged", "Merged", {"1", "2","3"}, {"1", "2", "3"})
in
#"Expanded Merged1"

You can copy and update my code steps after #"Removed Columns".  You need to add an Index for your lists and these numbers will be column headers after you pivot the list column. Here I have three lists in one column, so I create an Index from 1 to 3. Then merge and expand merge by M query.

Result is as below.

1.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
SVUser123
Frequent Visitor

I did try to use the Split function as

Table.SplitColumn(reqTable, "Column1", Splitter.SplitTextByDelimiter(",")) 

but got an error saying "We cannot conver a value of type List to type Text"

Hi @SVUser123 

I create a sample to have a test.

Sample:

1.png

My Code:

let
Source = #table({"1", "2","3"}, {{ {1,2}, {3,4},{4,5} }}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Value"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns", {{"Index", type text}}, "en-US")[Index]), "Index", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2","3"}, each List.Transform(List.Zip(_), each Record.FromList(_,{"1","2","3"})),"Merged"),
    #"Expanded Merged" = Table.ExpandListColumn(#"Merged Columns", "Merged"),
    #"Expanded Merged1" = Table.ExpandRecordColumn(#"Expanded Merged", "Merged", {"1", "2","3"}, {"1", "2", "3"})
in
#"Expanded Merged1"

You can copy and update my code steps after #"Removed Columns".  You need to add an Index for your lists and these numbers will be column headers after you pivot the list column. Here I have three lists in one column, so I create an Index from 1 to 3. Then merge and expand merge by M query.

Result is as below.

1.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.