- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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???
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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... 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That did it!! 😄
High Five!!*
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Alright, cool beans.
I also think I'm trying to put this code in the wrong place. Will keep you posted...
Thanks! 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What does the #"previous step" mean??
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
06-10-2024 03:14 AM | |||
02-23-2024 01:11 PM | |||
10-17-2023 12:49 AM | |||
06-05-2024 01:31 PM | |||
03-14-2024 05:06 AM |
User | Count |
---|---|
113 | |
80 | |
55 | |
54 | |
44 |
User | Count |
---|---|
168 | |
114 | |
74 | |
61 | |
53 |