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 Label4947435965 0002 Beratung 0004 Online Marketing 0006 HubSpot 02.1 StrategieIn 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!

freddy21

Frequent Visitor

9 months ago

Solved

Table Transformation

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!

Need Help

jgeddes

9 months ago

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"

ronrsnfld

9 months ago

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"

6 Replies

jgeddes

Super User

9 months ago

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"

v-dineshya
Community Support
9 months ago
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
  Community Support
  9 months ago
  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

ronrsnfld

Super User

9 months ago

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"

wdx223_Daniel
Community Champion
9 months ago
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])

aTa_Shaikh

Frequent Visitor

8 months ago

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

Forum Discussion

Table Transformation

6 Replies

Related content

Table Transformation

Table Transformation

Transform columns / type

Table Transformation

Transforming Table