excelpowerquery

Using Excel Power Query, group data based on column values

I have a table of data like this:
enter image description here

I want the data to look like this:
enter image description here

I have tried various forms of groupings and pivot columns. This is the closest I have come:
enter image description here

Can someone suggest the best way to format the rows and columns? Thanks

Solution

  • You need to group by each set of values defined by the items that start with "Party" in the FacilityKey column.

    Then pivot each sub-table.

    Data
    enter image description here

    You can use the fourth and fifth arguments of the Table.Group function to do that

    M-Code

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Facility", type text}, {"FacilityKey", type text}, {"FacilityValue", type any}}),
   
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Facility", "FacilityKey"}, {
        {"all", each Table.Pivot(_, List.Distinct([FacilityKey]),"FacilityKey","FacilityValue"),
            type table[Facility=text,Party=text, Id=any, Abbrev=nullable text, Name=nullable text, Type=nullable text]   }},
        GroupKind.Local, (x,y)=>Number.From(x[FacilityKey]="Party" and y[FacilityKey]="Party")),
   
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Facility", "FacilityKey"}),
    #"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", {"Facility", "Party", "Id", "Abbrev", "Name", "Type"})
in
    #"Expanded all"

    Results
    enter image description here