Forum Discussion
REVISED QUESTION: Filtering issues in column
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
very strange.
M is case sensitive - so does "shipping" actually match your text or should it be "Shipping" instead?
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.
Instead of creating a calculated column using DAX I recommend to create a custom column in the query editor using M.
Always.
?
Because it compresses better, has a richer library function and is easier to write in most of the cases.
You can add a custom column using the UID and then you have to type in the condition (red). The full code for this step would look like this at the end:
ShippingCharges = Table.AddColumn(#"PreviousStep", "ShippingCharges", each if Text.Contains([lineItemcolumn], "shipping") then [lineItemAppliedAmount] else "")
Make sure your columns are formatted correctly before applying this step.
What does the #"previous step" mean??
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
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
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"