Table Transformation

[freddy21]

Hello everyone,

I have the following problem:
I want to display the following table, which currently looks like this:

I want this:

Id		Label1		Label2		Label3		Label4
947435965		0002 Beratung		0004 Online Marketing		0006 HubSpot		02.1 Strategie

In this example, the ID 947435965 has 4 labels. This varies — meaning there are IDs with up to 10 labels, and others with only one label.

How can I solve this?
Thank you in advance!

[jgeddes · Accepted solution]

Add an Index column starting at 1 and incrementing by 1.

Replace the values in the Index column, adding the 'Label' prefix.

Pivot the transformed Index column. Use the 'labels' column as the Values column with no aggregation.

Complete example code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQxNzE2tTQzVdJRMjAwMFJwSi1KLCnNS1eK1cGQNVHwz8vJzEtV8E0syk4tycSuykzBozQpuCC/BFPSSM9QIbgEaEFqemaqUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, labels = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"labels", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Index], each "Label"&Number.ToText([Index]),Replacer.ReplaceValue,{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Index]), "Index", "labels")
in
    #"Pivoted Column"

[aTa_Shaikh]

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQxNzE2tTQzVdJRMjAwMFJwSi1KLCnNS1eK1cGQNVHwz8vJzEtV8E0syk4tycSuykzBozQpuCC/BFPSSM9QIbgEaEFqemYqRNrQ0MjYxNTMHKLXUiEjPysRU8bQwFAhLb+ouCQxJ0cpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, labels = _t]),
Group = Table.Group( Source , "id" , {"x" , each
Table.TransformColumnNames(Table.FromRows( {_[labels]} ) , each Text.Replace(_ , "Column" , "label") ) } ),
toTbls = Table.FromPartitions( "ID" , Table.ToRows(Group) , type number )
in
toTbls

You Can Use This Method in the future 

[wdx223_Daniel]

NewStep = Table.Combine(Table.Group(YourTable,"Id",{"n",each #table({"Id"}&List.Transform(List.Positions([Labels]),each "Labels "&Text.From(_+1)),{{[Id]{0}}&[Labels]})})[n])

[ronrsnfld · Accepted solution]

With more than one ID, you can 

• Group by ID
• Create a list of Field Names dependent on the number of rows in the grouped table.
• Create a list of records from the label values and the field names
• Expand the records into new rows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQxNzE2tTQzVdJRMjAwMFJwSi1KLCnNS1eK1cGQNVHwz8vJzEtV8E0syk4tycSuykzBozQpuCC/BFPSSM9QIbgEaEFqemYqRNrQ0MjYxNTMHKLXUiEjPysRU8bQwFAhLb+ouCQxJ0cpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, labels = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"labels", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
        {"Row", (t)=> 
            [a=List.Repeat({"Label"}, Table.RowCount(t)),
             b=List.Transform(List.Numbers(1, List.Count(a)), each Text.From(_)),
             c=List.Zip({a,b}),
             d=List.Transform(c, each Text.Combine(_, " ")),
             e=Record.FromList(t[labels],d)][e]}}),
    
    #"Field Names" = List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[Row], each Record.FieldNames(_)))),
    #"Expanded Row" = Table.ExpandRecordColumn(#"Grouped Rows", "Row", #"Field Names")
in
    #"Expanded Row"

[v-dineshya]

Hi @freddy21 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @jgeddes , Thank you for the prompt response.

Hi @freddy21 , Could you please try the proposed solution shared by  @jgeddes ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[v-dineshya]

Hi @freddy21 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[jgeddes · Accepted solution]

Add an Index column starting at 1 and incrementing by 1.

Replace the values in the Index column, adding the 'Label' prefix.

Pivot the transformed Index column. Use the 'labels' column as the Values column with no aggregation.

Complete example code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQxNzE2tTQzVdJRMjAwMFJwSi1KLCnNS1eK1cGQNVHwz8vJzEtV8E0syk4tycSuykzBozQpuCC/BFPSSM9QIbgEaEFqemaqUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, labels = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"labels", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Index], each "Label"&Number.ToText([Index]),Replacer.ReplaceValue,{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Index]), "Index", "labels")
in
    #"Pivoted Column"