REVISED QUESTION: Filtering issues in column

[heathernicole]

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.

1. item
2. invoice #
3. invoiced amount
4. 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 
5. Unit price (for each item)

Because shipping charges is a line item - it's causing issues. Any ideas whatsoever??? 

[ImkeF · Accepted solution]

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

[ImkeF · Accepted solution]

very strange.

M is case sensitive - so does "shipping" actually match your text or should it be "Shipping" instead?

[ImkeF]

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.

[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"

[heathernicole]

Here's as far as I get...

I don't think I'm inserting it right. Trying to research how utilized M in Power BI to try and get a better understanding. Unfortunately my deadline comes a lot faster than my M ability... 🙂 

[ImkeF]

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"

[heathernicole]

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. 

[ImkeF · Accepted solution]

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

[heathernicole]

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...

[ImkeF · Accepted solution]

very strange.

M is case sensitive - so does "shipping" actually match your text or should it be "Shipping" instead?

[heathernicole]

That did it!! 😄 

High Five!!*

[ImkeF · Accepted solution]

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.

[heathernicole]

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. 

[ImkeF]

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/

[heathernicole]

I think we've got it!!!! 😄 : 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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[heathernicole]

Alright, cool beans. 

I also think I'm trying to put this code in the wrong place. Will keep you posted...

Thanks! 🙂 

[ImkeF]

in the advanced editor:

In Query Editor: Home -> Query -> Advanced Editor

[ImkeF]

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]

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]

it's giving an error for what I tried - it's on a separate computer, so I can't copy and paste.

Here's the code:

Shipping Charges = Table.AddColumn(#"PreviousStep", "ShippingCharges", each if Text.Contains([Sales Line Item Description]. "shipping") then [Sales Line Item Sales Amount] else 0)

The Table I'm trying to put it in is called Sales Details