Can Power Query tabularize data with different levels of sub-total detail?-CodePudding

I am a proficient Excel user but new to Power Queries - they seem powerful, but I am hung up tabularizing a Quickbooks report.

In short, the data includes several columns of hierarchy all expanded to the lowest node and includes sub-headers and sub-totals. I need to tabularize this data, but when one section has detail 3 columns deep and the next section only goes 2 columns deep, "Fill Down" (in the 3rd column) incorrectly fills sub-headers in a different section.

If this does not make sense, I put together a screenshot that hopefully illustrates this question a little better:

Thank you in advance, Dillon

CodePudding user response：

This is a PivotTable right? Go to "Design" -> drop down "Report Layout" -> Show in Tabular Form

CodePudding user response：

If what you are showing is not a pivot table, but rather the output from Quicken, then you can:

Fill Down Column 1 only
Group by Column 1
Use a custom aggregation to Fill down column 2 in each subtable
Then expand the subtables
Remove the Amount rows that contain null
reset the data types

let

//cahnge table name in next line to the real table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Column1", type text}, {"Column2", type text}, {"Item", type text}, {"Amount", Currency.Type}}),

//Fill down the main group
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Column1"}),

//group by Column 1; then fill down subgroup in column 2
    group = Table.Group(#"Filled Down","Column1",{
        {"subGroup", each Table.FillDown(_,{"Column2"})}
        }),
    #"Expanded subGroup" = Table.ExpandTableColumn(group, "subGroup", {"Column2", "Item", "Amount"}, {"Column2", "Item", "Amount"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded subGroup", each ([Amount] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Column2", type text}, {"Item", type text}, {"Amount", Currency.Type}})
in
    #"Changed Type1"