Forum Discussion
Solved
Transform table structure
Hi,
I have this Table 1 structure and I need to convert it to the Table 2 structure, mainly the values for the same ID to appear on a single row, separated by ;. Do you know how this can be achieved in Power Query?
Many thanks in advance for your support!
Table 1
| ID | Value |
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | x |
| 2 | y |
| 2 | z |
Table 2
| ID | Value |
| 1 | a;b;c |
| 2 | x;y;z |
let Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Transform-table-structure/m-p/2250024#M818814"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(6) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(6) > * > TR > :nth-child(2)"}}, [RowSelector="TABLE:nth-child(6) > * > TR"]), #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Value", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"ad", each _, type table [ID=nullable number, Value=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x=[ad], y=x[Value] in Text.Combine(y,":")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}) in #"Removed Columns"1) Group by ID (leave it on count as we will change later)
Results in:
2) Go into the advanced editor and change the line:
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.RowCount(_), Int64.Type}})to
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Combined", each Text.Combine([Value], "; "), type text}})
4 Replies
let Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Transform-table-structure/m-p/2250024#M818814"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(6) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(6) > * > TR > :nth-child(2)"}}, [RowSelector="TABLE:nth-child(6) > * > TR"]), #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Value", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"ad", each _, type table [ID=nullable number, Value=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x=[ad], y=x[Value] in Text.Combine(y,":")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}) in #"Removed Columns"1) Group by ID (leave it on count as we will change later)
Results in:
2) Go into the advanced editor and change the line:
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.RowCount(_), Int64.Type}})to
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Combined", each Text.Combine([Value], "; "), type text}})
