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.
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"
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.
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
I think we've got it!!!! :D : D :D :D :D :D
I'm double checking my figures to make sure it's right... but I think we've got it!!
I could hug you right now!!!
I'll get back with you!
THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Alright.
It's SO close...
The figures show up in the InvoicedAmount Column and nothing in the shipping column...
I still hasn't completely split the shipping column into itself yet... I need to see only the shipping charges in the shipping column and right now there's nothing. And Invoiced amount has all the items.
Working with it...
very strange.
M is case sensitive - so does "shipping" actually match your text or should it be "Shipping" instead?