Transform table structure

[andra2]

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

[smpa01 · Accepted solution]

@andra2 

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"

[bcdobbs · Accepted solution]

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

[Pragati11]

HI @andra2 ,

You can create a calculated column as follows:

Column =
CONCATENATEX (
    FILTER (
        SUMMARIZE ( Table2, Table2[ID], Table2[Value] ),
        Table2[ID] = EARLIER ( Table2[ID] )
    ),
    Table2[Value],
    ";"
)

This gives you required result:

Thanks,

Pragati

[Pragati11]

Hi @smpa01 ,

Curious to know if my solution is not right.

Just want to understand what stopped you from accepting it as a solution? 🙂

Thanks,

Pragati

[smpa01 · Accepted solution]

@andra2 

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"