Forum Discussion
SharePoint list expand table column
Hi all
Is there any way for me to expand the below column type, being pulled in from a Sharepoint list, in Power Query editor?
There are multiple name in the field on the Sharepoint list, so I need to extract these in PowerBI as concatenated for each row
Thanks!
Hi, PNicholsonRS
You can try to group by the first column and use list.combine to integrate the data after converting the data into a list.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUcVKK1YlWMkJjO+u4KMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Count", each _, type table [Column1=nullable number, Column2=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Column2]), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}) in #"Removed Columns"Best Regards,
Community Support Team _ Eason
3 Replies
PNicholsonRS , There is expand icon and end of the column name on the right size, is that not helping you?
amitchandak I have expanded the column, but this means multiple rows when I want the values concatenated to sit on the same row. e.g. the name values for rows 17-18 below need to sit on one single row
Hi, PNicholsonRS
You can try to group by the first column and use list.combine to integrate the data after converting the data into a list.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUcVKK1YlWMkJjO+u4KMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Count", each _, type table [Column1=nullable number, Column2=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Column2]), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}) in #"Removed Columns"Best Regards,
Community Support Team _ Eason
