Pivoting a column and merging linked fields

[Merleau]

Hello,

I have a table with multiple columns (more than 30) in no particular order. I need to create multiple columns by pivoting one of these columns. And populate them with associated information coming from another column.

The original table is:

ID	Type	Programs	Name
ID1	AM	EnglishAM	ID1-name
ID1	PM	SpanishPM	ID1-name
ID1	AM	ItalianAM	ID1-name
ID1	AM	FrenchAM	ID1-name
ID2	AM	FrenchAM	ID2-name
ID2	PM	CoreanPM	ID2-name
ID2	AM	EnglishAM	ID2-name
ID3	PM	ArabicPM	ID3-name
ID3	PM	ItalianPM	ID3-name
ID1	AM-Plus	GreekAM-Plus	ID1-name

After creating the columns, I would like to obtain the table below.

ID	AM	PM	AM-Plus	Name
ID1	EnglishAM, ItalianAM, FrenchAM	SpanishPM	GreekAM-Plus	ID1-name
ID2	FrenchAM, EnglishAM	CoreanPM		ID2-name
ID3		ArabicPM, ItalianPM		ID3-name

Can somebody pls help?

 Thank you

[ImkeF · Accepted solution]

Hi @Merleau ,

you can pivot on the Type-column and use a Text.Combine-function as the aggregation function on the "Programs"-column.
Create a new query and paste this code into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRcvQFEq556TmZxRlgNlBYNy8xN1UpVgemJgAkHlyQmAdUE4BDDURvSWJOZmIeLnPA4m5FqXnJWK0ywqrECF0J2AXO+UWpiXkBOJRg8RSKGmOYMY5FiUmZyVBjjLEqgfoJmxqIn3QDckqLgSz3otTUbAQX4blYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Programs = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", type text}, {"Programs", type text}, {"Name", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Programs", each Text.Combine(_, ", "))
in
    #"Pivoted Column"

Enclosing a file a well.

[ImkeF · Accepted solution]

Hi @Merleau ,

you can pivot on the Type-column and use a Text.Combine-function as the aggregation function on the "Programs"-column.
Create a new query and paste this code into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRcvQFEq556TmZxRlgNlBYNy8xN1UpVgemJgAkHlyQmAdUE4BDDURvSWJOZmIeLnPA4m5FqXnJWK0ywqrECF0J2AXO+UWpiXkBOJRg8RSKGmOYMY5FiUmZyVBjjLEqgfoJmxqIn3QDckqLgSz3otTUbAQX4blYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Programs = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", type text}, {"Programs", type text}, {"Name", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Programs", each Text.Combine(_, ", "))
in
    #"Pivoted Column"

Enclosing a file a well.

[Anonymous]

it looks very similar to a recently treated problem

let
    Source = Table.FromRecords (Json.Document (Binary.Decompress (Binary.FromText("i65W8nRRsgIShko6SiGVBalAjqMvkB1QlJ9elJhbDOS75qXnZBZngIX9EnNTIep180DMWh24CUa4TXArSs1LRjPACMMAY4QBAWgGOBYlJmUmB6AYYIxhgCFuA4ILEvOAfggg3g/oJjjnF6Um5gUQ8AOeYPQsSczJTMyjIBixRgRJ4Qh1AwkBSUxUwjwRCwA=",BinaryEncoding.Base64),Compression.Deflate))),

    group = Table.Group(Source, "ID", {"Type-Programs", each Table.PromoteHeaders(Table.Transpose(Table.Group(_, "Type", {"_", each Text.Combine(_[Programs], ", ")})))}),
    tc=Table.Combine(group[#"Type-Programs"])
in
    tc

[Merleau]

I solved it using the code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKqSxIBVIBRfnpRYm5xUCmX2JuqlKsDkjeEMh19AUSrnnpOZnFGWA2UFg3D6HGCKbGrSg1LxmmxAhZiTHIApC4Y1FiUmZyAESJMbISQ5iS4ILEPKBNAThsAos75xelJuYFYLEJ7mDPksSczMQ8vA5G9RR2F0PNweVkTI/DrIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID", "Type"}, {{"Count", each _, type table [ID=text, Type=text, Programs=text, Name=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Programs]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Custom.1", each [Count][Name]{0}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Custom")
in
    #"Pivoted Column"

But @ImkeF's is so much cleaner.

I will accept it as a solution.

Thank you so much for all your contribution.

[Merleau]

Sincere thanks to all for the solution.

How do I sort the resulting list pls? Just normal ascending order.

I would like the final list to be ordered either in M or in DAX.

That is the last requirement for this task. Sorry that I mention it this late.

Again many thanks.

[Anonymous · Accepted solution]

I'm not sure I understand this request correctly.

I guess this modification of this expression:

Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Programs])

to

Table.AddColumn(#"Grouped Rows", "Custom", each List.Sort([Count][Programs]))

[Merleau]

Thank you so much for all your solution and contribution.

I will accept @Anonymous's last suggestion as a response because it works.

However, I am still looking for a solution in DAX.

Thank you

[ImkeF]

Hi @Merleau ,

you have to sort it in the report.

Due to Power BIs underlying VertiPaq engine, you cannot enforce a sort order in the data model.