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.
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"
Alright, cool beans.
I also think I'm trying to put this code in the wrong place. Will keep you posted...
Thanks! :)
in the advanced editor:
In Query Editor: Home -> Query -> Advanced Editor