Convert cell list in Col1 to separate columns

[SVUser123]

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 

[SVUser123]

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"

[v-rzhou-msft · Accepted solution]

Hi @SVUser123 

I create a sample to have a test.

Sample:

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.

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.