Forum Discussion
Solved
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 ...
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
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