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 ach...
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}})
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}})