Transforming a Table in Excel Power Query: Grouping and Splitting Rows Based on Unique Order IDs

[aamaramaster]

Hi, I have an Excel table with three columns: Order ID, Names, and Event. The Order ID column contains duplicate values corresponding to different names and events. I need assistance in transforming this table using Power Query in Excel to achieve the following structure:

1. Unique Order ID: Each Order ID should appear only once.
2. Names Columns: For each Order ID, the names associated with it should be spread across multiple columns (e.g., Name 1, Name 2, Name 3, etc.).
3. Events Columns: Similarly, for each Order ID, the events associated with it should be spread across multiple columns (e.g., Event 1, Event 2, Event 3, etc.).

Original Table:

Order ID	Names	Event
1	JAN	MAC
1	Berry	MAC
1	Ricky	FAN
2	Sara	SAN
2	Nada	SAN
3	Jasmine	BAN

Desired Output:

Order ID	Name 1	Name 2	Name 3	Event 1	Event 2
1	JAN	Berry	Ricky	MAC	FAN
2	Sara	Nada		SAN
3	Jasmine			BAN

 

[aamaramaster]

Hi, I have an Excel table with three columns: Order ID, Names, and Event. The Order ID column contains duplicate values corresponding to different names and events. I need assistance in transforming this table using Power Query in Excel to achieve the following structure:

1. Unique Order ID: Each Order ID should appear only once.
2. Names Columns: For each Order ID, the names associated with it should be spread across multiple columns (e.g., Name 1, Name 2, Name 3, etc.).
3. Events Columns: Similarly, for each Order ID, the events associated with it should be spread across multiple columns (e.g., Event 1, Event 2, Event 3, etc.).

Original Table:

Order ID	Names	Event
1	JAN	MAC
1	Berry	MAC
1	Ricky	FAN
2	Sara	SAN
2	Nada	SAN
3	Jasmine	BAN

Desired Output:

Order ID	Name 1	Name 2	Name 3	Event 1	Event 2
1	JAN	Berry	Ricky	MAC	FAN
2	Sara	Nada		SAN
3	Jasmine			BAN

I do not want to use DAX if possible

 

[AlienSx]

a little bit longer than expected...

let
    // calculates max number of items in the list of lists
    max_count = (lst) => List.Max(List.Transform(lst, (x) => List.Count(x))),
    // splits list column and creates custom column names
    exp_column = (tbl, column_name) => Table.SplitColumn(
        tbl,
        column_name,
        (x) => x,
        List.Transform(
            {1..max_count(Table.Column(tbl, column_name))},
            (x) => column_name & " " & Text.From(x)
        )
    ),
    // your source table
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    // just group by id and collect names and events into distinct lists
    group = Table.Buffer(
        Table.Group(
            Source,
            "Order ID",
            {
                {"Name", (x) => List.Distinct(x[Names])},
                {"Event", (x) => List.Distinct(x[Event])}
            }
        )
    ),
    // apply our functions to finish this job
    exp_names = exp_column(group, "Name"),
    exp_events = exp_column(exp_names, "Event")
in
    exp_events

 

[alex78]

Another attempt

let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.Group(A, {"Order ID"}, {{"x", each 
    [ 
    a = List.Distinct([Names]), 
    b = List.Distinct([Event]), 
    c = List.Combine(List.Transform(List.Zip({{{1..List.Count(a)},{1..List.Count(b)}}, {"Name", "Event"}}), (x)=> List.Transform(x{0}, each x{1} & " " & Text.From (_)))), 
    d = Table.Combine(List.Transform({List.Zip({c, a & b})}, each Table.PromoteHeaders(Table.FromColumns(_)))) 
    ] [d]}}),
C = Table.ExpandTableColumn(B, "x", List.Distinct(List.Combine(List.Transform(B[x], Table.ColumnNames))))
in
C