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.

     

     

heathernicole's avatar
heathernicole
Continued Contributor
10 years ago

Agreed - 

 

This file seems to show what I'm needing to do . And yes, you have the basic idea of the table structure. There's about 30 more fields but that's definitely it. 

 

You used the code you posted earlier to do this?

 

 

ImkeF's avatar
ImkeF
Community Champion
10 years ago

Almost - a little modification and an additional step:

 

let
    Source = ...,
    #"Changed Type" = Table.TransformColumnTypes(Source,....),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Shipping", each if Text.Contains([lineItemcolumn], "shipping") then "shipping" else "InvoicedAmount"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Shipping]), "Shipping", "lineItemAppliedAmount")
in
    #"Pivoted Column"

  • heathernicole's avatar
    heathernicole
    Continued Contributor
    10 years ago

    Alright, cool beans. 

     

    I also think I'm trying to put this code in the wrong place. Will keep you posted...

     

    Thanks! :) 

    • ImkeF's avatar
      ImkeF
      Community Champion
      10 years ago

      in the advanced editor:

      In Query Editor: Home -> Query -> Advanced Editor

  • heathernicole's avatar
    heathernicole
    Continued Contributor
    10 years ago

    Here's as far as I get...

     

     

    I don't think I'm inserting it right. Trying to research how utilized M in Power BI to try and get a better understanding. Unfortunately my deadline comes a lot faster than my M ability... :) 

     

    • ImkeF's avatar
      ImkeF
      Community Champion
      10 years ago

      Yes, you're entering the full code in the editor-window of the "Add-custom-column-step" instead of the advanced query editor.

       

      But we can work from here as well - might be easier now.

      In this case you just have to paste in this:

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

       

      click OK and then perform the last step:

       

      check column: "Shipping",

      then: Transform - Any Column - Pivot Column -

       

      Choose: your column "lineItemAppliedAmount" in "Values Column"

      • heathernicole's avatar
        heathernicole
        Continued Contributor
        10 years ago

        Getting closer... :) 

         

        I got an error: 

         

        And I found an answer on stackoverflow  but I'm researching on exactly what it means and how I need to fix it.