Pivoting a column and merging linked fields with count

[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 the columns. Then populate the resulting columns with associated information coming from another column. The end result is in form of an ordered list. I also need to display the count of each list. For example, 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	NM	GreekNM	ID1-name

I can generate the following table.

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

However, I need help to include the columns CountXX.

CountXX counts the numbers of items in each “Programs” list per ID.

ID	AM	CountAM	PM	CountPM	NM	CountNM	Name
ID1	EnglishAM, FrenchAM, ItalianAM	3	SpanishPM	1	GreekNM	1	ID1-name
ID2	EnglishAM, FrenchAM	2	CoreanPM	1		0	ID2-name
ID3		0	ArabicPM, ItalianPM	2		0	ID3-name

Can somebody pls help?

With the code below, I only get one column (CountProgram) instead of 3 to account for all the “Programs”.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKqSxIBVIBRfnpRYm5xUCmX2JuqlKsDkjeEMh19AUSrnnpOZnFGWA2UFg3D6HGCKbGrSg1LxmmxAhZiTHIApC4Y1FiUmZyAESJMbISQ5iS4ILEPKBNAThsAos75xelJuYFYLEJ7mDPksSczMQ8vA5G9RR2F0PNweVkTI8boivxA4m7F6WmZvuhqogFAA==", 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 List.Sort([Count][Programs])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values", "CountProgramType", each List.Count([Count][Type])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom2", "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"

 Thank you

[Merleau]

Thanks so much. It does work indeed.

[Merleau]

Thank you Alexis, Your approach definitely appears to work and it is very clever. How to sort the list in this structure? This requirement is not in your solution. Thanks.

[AlexisOlson]

In the #"Grouped Rows" step, replace Text.Combine([Program]) with Text.Combine(List.Sort([Program])).

[AlexisOlson · Accepted solution]

This isn't especially elegant but I think it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKqSxIBVIBRfnpRYm5xUCmX2JuqlKsDkjeEMh19AUSrnnpOZnFGWA2UFg3D6HGCKbGrSg1LxmmxAhZiTHIApC4Y1FiUmZyAESJMbISQ5iS4ILEPKBNAThsAos75xelJuYFYLEJ7mDPksSczMQ8vA5G9RR2F0PNweVkTI8boivxA4m7F6WmZvuhqogFAA==", 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", "Name", "Type"}, {{"Combine", each Text.Combine([Programs], ", "), type nullable text}, {"Count", each Table.RowCount(_), type text}}),
    #"Removed Count" = Table.RemoveColumns(#"Grouped Rows",{"Count"}),
    #"Removed Combine" = Table.RemoveColumns(#"Grouped Rows",{"Combine"}),
    #"Add Count Text" = Table.TransformColumns(#"Removed Combine",{{"Type", each "Count" & _, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Add Count Text",{{"Count", "Combine"}}),
    #"Appended Query" = Table.Combine({#"Removed Count", #"Renamed Columns"}),
    #"Pivoted Column" = Table.Pivot(#"Appended Query", List.Distinct(#"Appended Query"[Type]), "Type", "Combine"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"ID", "Name", "AM", "CountAM", "PM", "CountPM", "NM", "CountNM"})
in
    #"Reordered Columns"