Convert a multiple IF formula from DAX to M

[AliceW]

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

[v-diye-msft]

Hi @AliceW 

Please provide the dummy pbix that we can test it on the exit data directly. 

[harshnathani]

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

[lbendlin]

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.