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

DH 1 Reputation point
2021-09-07T22:36:07.2+00:00

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.

130006-image.png

Thanks.

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
    2021-09-08T07:55:05.26+00:00

    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

    0 comments No comments

  2. Ron Rosenfeld 271 Reputation points
    2021-09-08T12:29:40.957+00:00

    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

    130205-untitled.png

    Results

    130139-res.png

    0 comments No comments