Multi-column Join pivot or group in power query-CodePudding

Is it possible to transform a table in Excel with power query with output similar to a MSSQL group by statement? I've looked at pivot and group by, but I've not found how to execute on only some of the source columns in power query.

I would normally use a SQL query something along the lines of...

SELECT val1.Attr1, val1.Attr2, val1.Attr4, val2.Attr4, val3.Attr4
FROM TABLE as val1
FULL OUTER JOIN TABLE as val2
ON val1.Attr1 = val2.Attr1 AND val1.Attr2 = val2.Attr2 
FULL OUTER JOIN TABLE as val3
ON val1.Attr1 = val3.Attr1 AND val1.Attr2 = val3.Attr2 
GROUP BY val1.Attr1, val1.Attr2, val1.Attr3

...but alas, I can only use Excel for this project. The above query hasn't been tested so my syntax may be off.

Sample source data

Attr1     | Attr2     | Attr3      | Attr4
---------------------------------------------
Name 1    | Item 1    | Value 1    | Value A
Name 1    | Item 1    | Value 2    | Value A
Name 1    | Item 1    | Value 3    | Value A
Name 1    | Item 2    | Value 1    | Value B
Name 1    | Item 2    | Value 2    | Value B
Name 1    | Item 2    | Value 3    | Value B
Name 1    | Item 3    | Value 1    | Value C
Name 2    | Item 1    | Value 1    | Value D
Name 2    | Item 1    | Value 2    | Value D
Name 2    | Item 1    | Value 3    | Value D
Name 2    | Item 2    | Value 1    | Value E
Name 2    | Item 2    | Value 2    | Value E
Name 2    | Item 2    | Value 3    | Value E
Name 2    | Item 3    | Value 3    | Value F

Desired output format in a single table

Attr1     | Attr2     | Value 1    | Value 2    | Value 3
----------------------------------------------------------
Name 1    | Item 1    | Value A    | Value A    | Value A
Name 1    | Item 2    | Value B    | Value B    | Value B
Name 1    | Item 3    | Value C    | null       | null
Name 2    | Item 1    | Value D    | Value D    | Value D
Name 2    | Item 2    | Value E    | Value E    | Value E
Name 2    | Item 3    | Value F    | null       | null

CodePudding user response：

You can Group by Attr1 and Attr2

Then Pivot each subtable on Attr3 with values of Attr4

let

//Change next two lines to reflect your actual table name
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Attr1", type text}, {"Attr2", type text}, {"Attr3", type text}, {"Attr4", type text}}),
    
//Group by Attr1 and Attr2
// Then Pivot on Attr3 values in Attr4
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Attr1", "Attr2"}, {
        {"Pivot", each Table.Pivot(_, List.Distinct([Attr3]),"Attr3","Attr4")}
        }),
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Grouped Rows", "Pivot", {"Value 1", "Value 2", "Value 3"}, {"Value 1", "Value 2", "Value 3"})
in
    #"Expanded Pivot"