How to convert dynamic multi-column sales data into rows for accounting system import?

Question

Hello,

I'm trying to figure out how to get power query to add rows based on optional sales add-ons for products, while maintaining a consistent order number. I've included an example image below of what I'm trying to accomplish. There's always a base model, but there can be any number of add-ons depending on the customer. Currently our accounting team has to parse this by hand and turn it into a simple list for uploading.

Thanks.

Answer 1

Hey!
This is actually in page 209 (Chapter 13) of a book that I wrote with a good friend of mine. The book is called 'Master your Data with Excel and Power BI' and the pattern is in the section that reads "Pivoting Horizontally stacked Sets"

Here's a link to a set of queries that simulates your full example:
https://gist.github.com/migueesc123/14f56fa40026e9aceb0d7d24eb2ddf36

Answer 2

Here is Power Query code to do that.

Please read the comments in the code and explore the Applied Steps to understand the algorithm

let  
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],  
  
//Don't need these columns  
    #"Removed Columns" = Table.RemoveColumns(Source,{"Product", "Sales Total"}),  
  
//create Base Table  
    base = Table.RenameColumns(  
        Table.SelectColumns(#"Removed Columns",{"OrderID", "Base Model"}),  
        {"Base Model", "Sales Total"}),  
  
    //add Product Column and set the order  
    baseTbl = Table.ReorderColumns(  
                Table.AddColumn(base, "Product", each "Base Model"),  
                {"OrderID", "Product", "Sales Total"}),  
  
//Process add-ons  
    ao = Table.RemoveColumns(#"Removed Columns","Base Model"),  
  
    //Unpivot, then delete blank Values  
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(ao, {"OrderID"}, "Attribute", "Value"),  
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),  
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Value] <> "")),  
  
    //add offset column to simplify combining addon description and price  
    nextRow = let   
                x = List.RemoveFirstN(#"Filtered Rows"[Value],1) & {null},  
                y = Table.ToColumns(#"Filtered Rows") & {x},  
                z = Table.FromColumns(y, Table.ColumnNames(#"Filtered Rows") & {"nextRow"})  
              in   
               z,  
  
    //add Index and IntegerDivision columns for grouping  
    #"Added Index" = Table.AddIndexColumn(nextRow, "Index", 0, 1, Int64.Type),  
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),  
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),  
  
    //Group and then extract relevant ID, Product and Price  
    #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Integer-Division"}, {  
        {"OrderID", each [OrderID]{0}, Text.Type},  
        {"Product", each [nextRow]{0}, Text.Type},  
        {"Sales Total", each [Value]{0}, Currency.Type}  
    }),  
  
    //remove now unneeded integer-division column  
    addOns = Table.RemoveColumns(#"Grouped Rows",{"Integer-Division"}),  
  
    //Combine with baseTable, then sort by ID  
    combine = Table.Combine({baseTbl, addOns}),  
    #"Sorted Rows" = Table.Sort(combine,{<!-- -->{"OrderID", Order.Ascending}}),  
  
//Add the Grand Total Row and combine with the main  
    gtTbl = Table.FromRecords({[OrderID = "Grand Total", Product = null, Sales Total = List.Sum(#"Sorted Rows"[Sales Total])]}),  
    withGT = Table.Combine({#"Sorted Rows", gtTbl}),  
//set data types  
    #"Changed Type" = Table.TransformColumnTypes(withGT,{  
        {"OrderID", type text},   
        {"Product", type text},   
        {"Sales Total", Currency.Type}})  
in  
    #"Changed Type"  

Original Data

Results