topic Re: REVISED QUESTION: Filtering issues in column in Desktop

REVISED QUESTION: Filtering issues in column

heathernicole — Thu, 10 Mar 2016 20:14:28 GMT

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:

Re: REVISED QUESTION: Filtering issues in column

ImkeF — Thu, 10 Mar 2016 15:51:42 GMT

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.

Re: REVISED QUESTION: Filtering issues in column

heathernicole — Thu, 10 Mar 2016 15:53:57 GMT

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!

Re: REVISED QUESTION: Filtering issues in column

heathernicole — Thu, 10 Mar 2016 15:59:03 GMT

What does the #"previous step" mean??

Re: REVISED QUESTION: Filtering issues in column

ImkeF — Thu, 10 Mar 2016 16:04:00 GMT

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

Re: REVISED QUESTION: Filtering issues in column

heathernicole — Thu, 10 Mar 2016 16:09:19 GMT

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

Re: REVISED QUESTION: Filtering issues in column

ImkeF — Thu, 10 Mar 2016 16:14:31 GMT

So your complete code should look like below, you should adjust the green part:

let
Source = .....,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"a", Int64.Type}}),

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

Re: REVISED QUESTION: Filtering issues in column

heathernicole — Thu, 10 Mar 2016 16:45:22 GMT

Ok so the "Changed Type" is the data type to change to?

I'm sorry - I'm trying to get my mind around this langauge - I haven't used up until this...

Re: REVISED QUESTION: Filtering issues in column

ImkeF — Thu, 10 Mar 2016 17:07:27 GMT

No need to apologize.

No, "Changed Type" is the name of the previous step (where an operation has taken place that changed the data types. The name of the step has been given by the system automatically).

All the comma-separated-parts of the code (lines) can be considered as autonomous variables that can be used everywhere in the query. Therefore in order to make a connection to each other they have to call each other. By using their names. In this case, the variable (stepname) "Shipping Charges" is calling the variable "Changed Type" to return the contents of it's columns [Sales Line Item Description] and [Sales Line Item Sales Amount] in order to make the comparisons as stated in the formula.

Does the code work now or do you need more help? I could load up a sample pbix workbook for you to investigate further.

Re: REVISED QUESTION: Filtering issues in column

heathernicole — Thu, 10 Mar 2016 18:01:51 GMT

Well I've tried entering it in - it's throwing an error at this moment and I'm trying to research why. It gives "Token Eof expected" and then highlights a comma after the first closing parentheses.

I'm looking into it...

Re: REVISED QUESTION: Filtering issues in column

Twan — Thu, 10 Mar 2016 18:15:54 GMT

I agree with ImkeF that the best way when considering performance is to solve this with the query editor using M. But if you are still learning M and want to solve this quickly using DAX and a calculated column, try adding this as a calculated column.

Shipping Charges = IF( CONTAINS('Sales Details', 'Sales Details'[Sales Line Item Description], "shipping"), 'Sales Details'[Sales Line Item Sales Amount], 0)

Re: REVISED QUESTION: Filtering issues in column

ImkeF — Thu, 10 Mar 2016 18:17:25 GMT

Very sorry, must be a comma instead of point before "shipping":

Source = .....,
#"Changed Type" = Table.TransformColumnTypes(Source, .....

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

Re: REVISED QUESTION: Filtering issues in column

heathernicole — Thu, 10 Mar 2016 20:05:10 GMT

Well that kind of did it.

I think I have a bigger problem than I realize.... I might need to change my question / approach...

Re: REVISED QUESTION: Filtering issues in column

ImkeF — Fri, 11 Mar 2016 10:03:20 GMT

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.

Re: REVISED QUESTION: Filtering issues in column

heathernicole — Fri, 11 Mar 2016 12:46:53 GMT

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?

Re: REVISED QUESTION: Filtering issues in column

ImkeF — Fri, 11 Mar 2016 14:32:02 GMT

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"

Re: REVISED QUESTION: Filtering issues in column

heathernicole — Fri, 11 Mar 2016 14:46:26 GMT

Alright, cool beans.

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

Thanks! :)

Re: REVISED QUESTION: Filtering issues in column

ImkeF — Fri, 11 Mar 2016 14:53:33 GMT

in the advanced editor:

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

Re: REVISED QUESTION: Filtering issues in column

heathernicole — Fri, 11 Mar 2016 14:57:01 GMT

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

Re: REVISED QUESTION: Filtering issues in column

ImkeF — Fri, 11 Mar 2016 15:09:48 GMT

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"