Forum Discussion
Continued ContributorREVISED QUESTION: Filtering issues in column
Community ChampionLooks 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.
Continued ContributorAgreed -
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
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
- 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"