Forum Discussion

UCK's avatar
UCK
New Member
6 years ago
Solved

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...
  • edhans's avatar
    edhans
    6 years ago

    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.

ImkeF's avatar
ImkeF
Community Champion
6 years ago

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's avatar
UCK
New Member
6 years ago

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

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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.