Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi folks,
I am terrible at M and I really need this formula in the Power Query so that I can then unpivot the table. Could anybody help me, please?
2019-01 =
SWITCH('Business'[Line],
"Lyme",
if(AND(related(Business[Close Date])<=date(2019,01,31),related(Business[Close Date])>=date(2019,01,01)),
[Amount]),
"Mountain",
if(and(related(Business[Start Date])<=date(2019,01,31),related(Business[End Date])>=date(2019,01,01)),
divide('Row'[Amount],'Row'[Term]))
"Earth",
if(and(related(Business[Start Date])<=date(2019,01,31),related(Business[Start Date])>=date(2019,01,01)),
('Row'[Amount]*0.6),
if(and(related(Opp[Start Date])<=date(2019,01,31),related(Opp[End Date])>=date(2019,01,01)),
divide('Row'[Amount]*0.4,'Row'[Term])))
)
If 'related' doesn't work, I can easily calculate these columns in the 'Row' table.
A big thank you.
Alice
Hi @AliceW
Please provide the dummy pbix that we can test it on the exit data directly.
Hi @AliceW ,
See if these blogs help you
https://www.youtube.com/watch?v=ivNtaAl7XNM
https://www.poweredsolutions.co/2019/06/09/logical-operators-and-nested-ifs-in-power-bi-power-query/
https://www.myonlinetraininghub.com/power-query-if-statements
https://exceloffthegrid.com/power-query-if-statements-for-conditional-logic/
https://www.excelguru.ca/blog/2014/08/27/the-if-function-in-power-query/
Regards,
Harsh Nathani
M is much simpler than DAX in this respect. All it has is and and or. No fancy switch syntax sugar.
Keep in mind that in Power Query your tables are not yet related. You either need to merge tables, or use custom column generator functions to lookup values in other tables.
Best would be to provide sample data for your row data source and business data source.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
75 | |
54 | |
38 | |
31 |
User | Count |
---|---|
99 | |
56 | |
50 | |
42 | |
40 |