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.
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
very strange.
M is case sensitive - so does "shipping" actually match your text or should it be "Shipping" instead?
That did it!! :D
High Five!!*
Have a look at this nice article that shows what pivot in Power Query actually does: http://blog.crossjoin.co.uk/2013/11/25/pivoting-data-in-power-query/
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...
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.
Now ... I want to make sure I understand exactly WHY that worked and what that little code did.
I've never used pivot columns before.
Can you give a breakdown of what we did - in layman's terms?
I think I know but I want to make sure I understand. I'm sure I'll have to do something similar to this many times over.