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

DH 1 Reputation point


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.



Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,104 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Miguel Escobar 326 Reputation points

    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:

    0 comments No comments

  2. Ron Rosenfeld 271 Reputation points

    Here is Power Query code to do that.

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

        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"})  
        //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}})  
        #"Changed Type"  

    Original Data




    0 comments No comments