Search code examples
exceloffice365powerquery

How do I Unpivot and Pivot with merged cells with PowerQuery

I have been tasked with taking data from an export and converting it to a report. My initial thought was to do it with VBA, but I think I can do it with Power Query, the problem is I know very little about it. But here is what I am looking to do.

enter image description here In the example above you can see the way the data comes to me Row 1 has currently 11 different categories that has 4 cells merged into 1. When I input it with Power Query the headers look like this

AS null null null BT null null null
Region Mgmt Area Area Date Used - AS Avail - AS % Booked - AS Status - AS Used - BT Avail - BT % Booked - BT Status - BT

What I would like my final result to be

3/1/2024 - Fri 3/2/2024 - Sat
Location Category Total Used Avail Available % Total Used Avail Available %
P1 - Nodes 100-500 AS 97 0 97 100% 1 0 1 100%
P1 - Nodes 100-500 BT 2 0 2 100% 96 20 76 79%
  1. The Categories in the first row will be put into a column
  2. Total will be the sum of Available and Used
  3. Available % is the avail/total
  4. The dates become the new grouping at the top
  5. The % booked and Status columns can be removed

I believe in order to accomplish what I am looking to do I will need to Unpivot and then re pivot the data. The issue I am running into is with the merged cells in the top row and how to get a unique list, remove the nulls, and move to a column

While playing with the Pivot and unpivot options I have tried to remove the first row Promote the new first row to headers, and Unpivot the data.

A kick in the right direction would be greatly appreciated as I am not sure if I am on the right track or not.

Solution

  • See if this gives you any ideas

    let   Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Top Rows" = Table.Skip(Source,1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Region", "Mgmt Area", "Area", "Date"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Type", "Category"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Region", "Mgmt Area"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Area", "Date", "Category"}, {
        {"total", each List.Sum(Table.SelectRows(_, each [Type] = "Avail" or [Type] = "Used")[Value])},
    {"avail", each List.Sum(Table.SelectRows(_, each [Type] = "Avail")[Value])},
    {"used", each List.Sum(Table.SelectRows(_, each [Type] = "Used")[Value])}

   }),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Available", each [avail]/[total],type number),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
#"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Changed Type", {"Area", "Date", "Category"}, "Attribute", "Value"),
#"Added Custom1" = Table.AddColumn(#"Unpivoted Other Columns1", "Custom", each Text.From([Date]) & "-" &[Attribute]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Date", "Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom]), "Custom", "Value", List.Sum)
in    #"Pivoted Column"

    enter image description here