Forum Discussion

heathernicole's avatar
heathernicole
Continued Contributor
10 years ago
Solved

REVISED QUESTION: Filtering issues in column

Original: Need to create a calculated column that says this:   Shipping charges = IF this "lineItemcolumn" contains "shipping", then give value of this "lineItemAppliedAmount".    No matter how I...
Need Help
  • ImkeF's avatar
    ImkeF
    10 years ago

    There must be blanks in the line item column then.

    In this case you need to wrap your formula in the custom column in a "try ... otherwise".

    So it looks like this:

     

    try if Text.Contains([lineItemcolumn], "shipping") then "shipping" else "InvoicedAmount" otherwise "InvoicedAmount"

     

    This will also allocate the Invoiced Amount if the column is blank

  • ImkeF's avatar
    ImkeF
    10 years ago

    very strange.

    M is case sensitive - so does "shipping" actually match your text or should it be "Shipping" instead?

     

  • ImkeF's avatar
    ImkeF
    10 years ago

    another check would be to use this code in the custom column instead:

     

    = try if Text.Contains([lineItemcolumn], "shipping") then "shipping" else "InvoicedAmount" otherwise "AllWrong"

     

    Then check in how the pivot looks: Do all numbers show up in "AllWrong"? Then sth in the Text.Contains - expression is completely wrong.

     

     

ImkeF's avatar
ImkeF
Community Champion
10 years ago

That should be the name of the previous step in your query.

If you paste the code of the table to adjust here, I can stitch it together for you.

 

Get code like this: In Query Editor: Home -> Query -> Advanced Editor

Check all and copy

heathernicole's avatar
heathernicole
Continued Contributor
10 years ago

it's giving an error for what I tried - it's on a separate computer, so I can't copy and paste.

 

Here's the code:

 

Shipping Charges = Table.AddColumn(#"PreviousStep", "ShippingCharges", each if Text.Contains([Sales Line Item Description]. "shipping") then [Sales Line Item Sales Amount] else 0)

 

The Table I'm trying to put it in is called Sales Details

  • ImkeF's avatar
    ImkeF
    Community Champion
    10 years ago

    So your complete code should look like below, you should adjust the green part: 

     

    let
        Source = .....,
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"a", Int64.Type}}),

        Shipping Charges = Table.AddColumn(#"PreviousStep", "Changed Type", each if Text.Contains([Sales Line Item Description]. "shipping") then [Sales Line Item Sales Amount] else 0)
    in
        #"Added Custom"

    • heathernicole's avatar
      heathernicole
      Continued Contributor
      10 years ago

      Ok so the "Changed Type" is the data type to change to?

       

      I'm sorry - I'm trying to get my mind around this langauge - I haven't used up until this...

      • ImkeF's avatar
        ImkeF
        Community Champion
        10 years ago

        No need to apologize.

         

        No, "Changed Type" is the name of the previous step (where an operation has taken place that changed the data types. The name of the step has been given by the system automatically).

         

        All the comma-separated-parts of the code (lines) can be considered as autonomous variables that can be used everywhere in the query. Therefore in order to make a connection to each other they have to call each other. By using their names. In this case, the variable (stepname) "Shipping Charges" is calling the variable "Changed Type" to return the contents of it's columns [Sales Line Item Description] and [Sales Line Item Sales Amount] in order to make the comparisons as stated in the formula.

         

        Does the code work now or do you need more help? I could load up a sample pbix workbook for you to investigate further.