Converting Vertical Transaction Data to Horizontal

[UCK]

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!

[Greg_Deckler]

@UCK - So you may want to pivot your value last few columns. @ImkeF and @edhans can probably be of better assistance.

[ImkeF]

Hi @UCK ,

you need an allocation of which which payment type belongs to Sales or Costs.
Does this column already exist in your source data table or do you have it in a different table?
Please specify.

[UCK]

No, there arent any columns that specify whether each payment type is a sales or cost

[edhans · Accepted solution]

@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.