Forum Discussion

hidalgo's avatar
hidalgo
Regular Visitor
8 years ago
Solved

A ? for a Report: How to Subtract Returned Items Quantities

  I have the following Purchasing Query that shows all rows -> purchases Receipts and Returns by Items. I need to group by Items and sumarize the ReceiptQty. (UnitCost, ExtCost and Volume won't cha...
  • kcantor's avatar
    kcantor
    8 years ago

    hidalgo

    In Query Editor create a conditional column for the if statement of Type.

    If [Type}="Return" then [ReceiptQty]*-1 else [ReceiptQty]

    Then you can group and the returns will be subtracted. 

  • hidalgo's avatar
    hidalgo
    8 years ago

    kcantor

     

    I created a conditional field as you told me called "Quantity"and it worked thanks. The returns now shows negative quantity.

    let
    Source = OData.Feed("------"),
    #"Post Purchases_table" = Source{[Name="Post Purchases",Signature="table"]}[Data],
    #"Added Conditional Column" = Table.AddColumn(#"Post Purchases_table", "Quantity", each if [Type] = "Return" then [ReceiptQty]*-1 else [ReceiptQty])
    in
    #"Added Conditional Column"

     

    THEN. I tried to create an aditional column called "Qty" for the report  to sumarize the Quantities so it could automatically substract those Returns with negative numbers BUT The designer shows the error in the picture below.

    How do I create that new Qty column to make it SUM those positive and negative Quantities?

kcantor's avatar
kcantor
Icon for Community Champion rankCommunity Champion
8 years ago

hidalgo

In Query Editor create a conditional column for the if statement of Type.

If [Type}="Return" then [ReceiptQty]*-1 else [ReceiptQty]

Then you can group and the returns will be subtracted. 

  • hidalgo's avatar
    hidalgo
    Regular Visitor
    8 years ago

    kcantor

     

    I created a conditional field as you told me called "Quantity"and it worked thanks. The returns now shows negative quantity.

    let
    Source = OData.Feed("------"),
    #"Post Purchases_table" = Source{[Name="Post Purchases",Signature="table"]}[Data],
    #"Added Conditional Column" = Table.AddColumn(#"Post Purchases_table", "Quantity", each if [Type] = "Return" then [ReceiptQty]*-1 else [ReceiptQty])
    in
    #"Added Conditional Column"

     

    THEN. I tried to create an aditional column called "Qty" for the report  to sumarize the Quantities so it could automatically substract those Returns with negative numbers BUT The designer shows the error in the picture below.

    How do I create that new Qty column to make it SUM those positive and negative Quantities?

    • kcantor's avatar
      kcantor
      Icon for Community Champion rankCommunity Champion
      8 years ago

      hidalgo

      In your M code it does not appear that the conditional column for Quantity had the data type changed. By default, powerquery uses data type any. You will need to go into Query Editor and change the data type of your new conditional column to a number and then your calculation will work properly.