Forum Discussion
Continued ContributorREVISED 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
- ImkeF
Community Champion
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.
- heathernicole
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!
- heathernicole
What does the #"previous step" mean??
- ImkeF
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
- heathernicole
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?
- ImkeF
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"
