Transform Data Table in Power Query

[ashameen01]

I am new to PQ, I need help on the following:

I have a source data in the following format in excel:

Licence	Year	Company	Category
LTE	2022	BNL	GD
LTE	2022	ELELL	LGL
LTE	2022	VECL	LGL
LTE	2023	RNG	LGL
LTE	2023	TOCE	LGL
LTE	2023	UTM	LGL
LTE	2023	RNG2	LGL
LTE	2023	SPL	GPL
LTE	2023	ACE	LGL
LTE	2024	ANDARA	GD
LTE	2024	OHUR	GPL
LTE	2024	RHFD	GPL
LTC	2022	INDL	GD
LTC	2023	FMG	GPL
LTC	2023	NALG	LGL
LTC	2023	SAPL	GD
LTC	2024	ANDARA	GD
AIHC	2022	COTT	GPL
AIHC	2023	POC	GPL
AIHC	2024	ODAF	GPL
LTO	2022	ASH	GPL
LTO	2022	NEP	GPL
LTO	2022	DFL	GD
LTO	2022	NOTR	GPL
LTO	2023	GRANVL	LGL
LTO	2024	GAIL	GPL

And I need to transform it to the following form in Power Query:

Licence	Year	GPL	GD	LGL	List of Companies granted
LTE	2022		1	2	VECL, BNL, ELELL
LTC	2022		1		INDL
AIHC	2022	1			COTT
LTO	2022	3	1		ASH, NEP, DFL,NOTR
LTE	2023	1		5	RNG, TOCE, UTM,RNG2,SPL,ACE
LTC	2023	1	1	1	FMG,NALG,SAPL
AIHC	2023	1			POC
LTO	2023			1	GRANVL
LTE	2024	2	1		OHUR,RHFD,ANDARA
LTC	2024		1		ANDARA
AIHC	2024	1			ODAF
LTO	2024	1			GAIL

Thank you for your kind help.

[AlienSx]

let
    Source = your_table,
    group = Table.Group(
        Source, {"Licence", "Year"}, 
        {{"x", (x) => Function.Invoke(
            Record.FromList,
            List.Reverse(
                Table.ToColumns(
                    Table.Group(x, "Category", {"count", Table.RowCount})
                )
            )
        )},
        {"List of companies", (x) => Text.Combine(List.Distinct(x[Company]), ", ")}}),
    xpand = Table.ExpandRecordColumn(group, "x", List.Distinct(Source[Category]))
in
    xpand

or slightly modified @jennratten 's code to avoid hard coded categories

let
    Source = your_table,
    lst = List.Transform(
        List.Distinct(Source[Category]), 
        (x) => {x, (y) => List.Count(List.Select(y[Category], (w) => w = x))}
    ),
    group = Table.Group(
        Source, {"Licence", "Year"}, 
        lst & {{"List of companies", (x) => Text.Combine(List.Distinct(x[Company]), ", ")}})
in
    group

[jennratten · Accepted solution]

Hello @ashameen01, you can achieve the desired result by grouping the rows by license and year, and then aggregating categories and companies by the groups.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJBDsIgEEWvYrp2YaoXGIHSJgiEoolpumhMY1zYGuPG2wsuBHRw02n6mD//T+m6QlxO43Qai2VxHIe7K2S+3obp6d+Gx3ie78+iX7pzlrlP5aosXdlK4Z6c/hImmPBMcPELD4ygbO2KkTyHrCIsx/Z290exzLFWvwNoBAE+bOORpGAASe6hqvcG0/TM1BVNGAlLaSRNlkmClWrHsS6PJIh0XxFrQWOKWAJo6sgKUdZGAwP0qlqRL7aIwlOoEqsqqEJb55BkOodolWSIm5Q1WJc3yQ3IQ3rHVHDJofn89v4F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Licence", type text}, {"Year", Int64.Type}, {"Company", type text}, {"Category", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Licence", "Year"}, {{"GPL", each List.Count ( List.Select ( _[Category], each _ = "GPL" ) ), type number }, {"GD", each List.Count ( List.Select ( _[Category], each _ = "GD" ) ), type number}, {"LGL", each List.Count ( List.Select ( _[Category], each _ = "LGL" ) ), type number}, {"Companies", each Text.Combine ( _[Company], ", " ), type nullable text}})
in
    #"Grouped Rows"

[ashameen01]

Many thanks, that worked, to get a wrap on the steps, so you grouped by "Licence" and "Year" without aggregation and added custom columns: " { {"GPL", each List.Count(List.Select(_[Category], each _ = "GPL")), type number}, {"GD", each List.Count(List.Select(_[Category], each _ = "GD")), type number}, {"LGL", each List.Count(List.Select(_[Category], each _ = "LGL")), type number}, {"Companies", each Text.Combine(_[Company], ", "), type nullable text} } )" to count categories and combined list of companies?

[jennratten]

Yes, that's correct!  Please let me know if I can further assist with this.

If this helped you please consider clicking Accept as Solution.