Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
heathernicole
Continued Contributor
Continued Contributor

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.

 

  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??? Smiley Sad

~heathernicoale
3 ACCEPTED SOLUTIONS

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

very strange.

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

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.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

28 REPLIES 28
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

heathernicole
Continued Contributor
Continued Contributor

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?

 

 

~heathernicoale

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

heathernicole
Continued Contributor
Continued Contributor

Here's as far as I get...

 

M script for shipping.JPG

 

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

 

~heathernicoale

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

heathernicole
Continued Contributor
Continued Contributor

Getting closer... 🙂 

 

I got an error: 

M Error.JPG

 

And I found an answer on stackoverflow  but I'm researching on exactly what it means and how I need to fix it. 

 

 

 

 

~heathernicoale

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

heathernicole
Continued Contributor
Continued Contributor

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

~heathernicoale

very strange.

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

heathernicole
Continued Contributor
Continued Contributor

That did it!! 😄 

 

High Five!!*

 

~heathernicoale

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.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

heathernicole
Continued Contributor
Continued Contributor

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. 

~heathernicoale

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/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

heathernicole
Continued Contributor
Continued Contributor

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

~heathernicoale
heathernicole
Continued Contributor
Continued Contributor

Alright, cool beans. 

 

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

 

Thanks! 🙂 

~heathernicoale

in the advanced editor:

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

heathernicole
Continued Contributor
Continued Contributor

What does the #"previous step" mean??

~heathernicoale

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

heathernicole
Continued Contributor
Continued Contributor

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

~heathernicoale

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.