Forum Discussion
Converting Vertical Transaction Data to Horizontal
Hi,
I currently have a data set which is vertical by nature and looks like the chart below. I was curious to know the best way to aggregate the data such that it would be easy for me to calculate the profit/loss for each transaction. Each Order ID will vary in the number of rows due to the number of unique transactions accociated with that order.
| Date | Order ID | SKU | Transaction Type | Payment Type | Payment Detail | Amount | Quantity |
| 05-Mar-20 | 0000001 | ABC-1 | Payment | Product Charge | $15 | 1 | |
| 05-Mar-20 | 0000001 | ABC-1 | Payment | Merchant Fee | Pick and Package | ($3) | |
| 05-Mar-20 | 0000001 | ABC-1 | Payment | Other | Shipping | ($5) | |
| 05-Mar-20 | 0000001 | ABC-1 | Payment | Rebates | ($2) | 1 | |
| 05-Mar-20 | 0000002 | ABC-2 | Payment | Product Charge | $20 | 1 | |
| 05-Mar-20 | 0000002 | ABC-2 | Payment | Merchant Fee | Pick and Package | ($5) | |
| 05-Mar-20 | 0000002 | ABC-2 | Payment | Promo Rebates | Shipping | $5 | |
| 05-Mar-20 | 0000002 | ABC-2 | Payment | Other | Shipping | ($5) | |
| 05-Mar-20 | 0000002 | ABC-2 | Payment | Rebates | ($1) | ||
| ... | ... | ... | ... | ... | ... | ... | ... |
Typically an order table would looks something like this which would make the calculations much easier
| Date | Order ID | SKU | Revenue | Cost | Profit |
| 05-Mar-20 | 0000001 | ABC-1 | 15 | 10 | 5 |
Thanks!
UCK take a look at this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZHRCoMgFIZfRcKLgoJy9ABbsLtYbJfRxZkdMiIL5y729lMbbAzHBOWI8H34/7ZtVGY1qIzlURoVZu8PVWZnA48ZpbYntfR3rkklQA1oLsyiRUkc0KUBhhoVFyA1OaLlm5FPBGRPGuATOGVMd4k1B+lOWqAy8yLGdR3l4PgynD/jFTTetiQxZYkvCXuh7H8XLPd24TeEdOHN8vNB80LeiT46oe6LwjThlfr5r0qLjeye", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Order ID" = _t, SKU = _t, #"Transaction Type" = _t, #"Payment Type" = _t, #"Payment Detail" = _t, Amount = _t, Quantity = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Payment Type", "Payment Detail"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged Transaction Type"), #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Amount", "Quantity"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged Amounts"), #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[#"Merged Transaction Type"]), "Merged Transaction Type", "Merged Amounts") in #"Pivoted Column"What this does is turns this:
Into something that looks like this:
This isn't done. You didn't say how you wanted the quantites to be handled. However, I now have every amount/qty in the same column (Product Charge, Rebate, etc) and you can now split those columns into the dollars and quantities, then convert them to numerical values. I kept them as text in the #"Changed Step" line. If the quantites are the same thing, you could just discard the extra quantity columns. Or if "Rebate" has a quantity of 1 in the first row, and Product Charges does too, then just leave those columns as seperate after you split them out.
Let me know if this helps get you started. If not, please be more explicit on what you expect the output to be. Your example didn't cover all of the info in the main table.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
