Forum Discussion
REVISED QUESTION: Filtering issues in column
Original: Need to create a calculated column that says this:
Shipping charges = IF this "lineItemcolumn" contains "shipping", then give value of this "lineItemAppliedAmount".
No matter how I try to do it - there seems to be a conflict with using text as the filter to display an integer...
Any help or leads in this area would be greatly appreciated!! :)
REVISED 3.10.16:
I have a bigger issue than orginally stated.
I'm creating a report from invoices. With the program we use (unfortunately) the shipping charges are listed as an line item. My original thought was to create a calculated column and isolate the shipping charges... my original approach is not working.
This is what I'm trying to do.
- item
- invoice #
- invoiced amount
- shipping charge (as a SEPARATE column) - when I try to isolate it by filtering it out or putting it into a different column in throws off the entire report and EVERYTHING is filtered by the shipping. I need it be listed as a separate column
- Unit price (for each item)
Because shipping charges is a line item - it's causing issues. Any ideas whatsoever??? :smileysad:
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.
28 Replies
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.
Hmm ok - I'll give it a go. I don't know M. At All.
But I'll give it a try and post back here.
Thanks!
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
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"
