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.
Looks like now there's no way around M any more :-)
Not sure if I got your table structure and requirement correctly, so please have a look at the file and let me know if/where things need to be changed. The code works the same in Power BI then.
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?
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! :)
