Forum Discussion
Solved
How to Convert DAX Code to Power Query M language
Hi, I have extrcted a table and I'm trying to add different columns to the table. However, while creating the column it's created with DAX code and I need to convert it to M language.
The table name is : F0901
DAX code:
Account lv3 =
SWITCH(TRUE(),
F0901[Obj Account]>= 1100 && F0901[Obj Account]<1230 ,"Cash & Banks",
F0901[Obj Account]>= 1230 && F0901[Obj Account]<1400 ,"Account Receivables & Others AR",
F0901[Obj Account]>= 1400 && F0901[Obj Account]<1800 ,"Inventory",
F0901[Obj Account]>= 1800 && F0901[Obj Account]<2000 ,"Const. In Progress",
F0901[Obj Account]>= 2000 && F0901[Obj Account]<3000 ,"Fixed Assets",
F0901[Obj Account]>= 3000 && F0901[Obj Account]<3670 ,"Current Libilities",
F0901[Obj Account]>= 3670 && F0901[Obj Account]<4500 ,"Long Term Libilities",
F0901[Obj Account]>= 4500 && F0901[Obj Account]<5000, "Owners'Equity",
F0901[Obj Account]>= 5011 && F0901[Obj Account]<5030, "Cash Sales",
F0901[Obj Account]>= 5030 && F0901[Obj Account]<5040, "Credit Sales",
F0901[Obj Account]>= 5040 && F0901[Obj Account]<6000, "Inter Company Sales",
F0901[Obj Account]= 6610 && F0901[Subsidiry Account]=1001 , "COGS-Cash Sales",
F0901[Obj Account]= 6610 && F0901[Subsidiry Account]=2001 , "COGS -Credit Sales",
F0901[Obj Account]= 6610 && F0901[Subsidiry Account]=3001 , "COGS-Inter Company Sales",
F0901[Obj Account]= 6610 && F0901[Subsidiry Account]>=5000 ,"Other Cost",
F0901[Obj Account] =6999 ,"Adjustment",
F0901[Obj Account]>=8000 && F0901[Obj Account]<8600, "Sales & Marketing Expenses",
F0901[Obj Account]>=8600 && F0901[Obj Account]<9300 ,"G& A Expenses",
F0901[Obj Account]>= 9300 ,"Financial Epenses")
M doesn't have a SWITCH function, but for what you are doing, that will be fine.
if [Obj Account]>= 1100 and [Obj Account]<1230 then "Cash & Banks" else if [Obj Account]>= 1230 and [Obj Account]<1400 then "Account Receivables & Others AR" else if [Obj Account]>= 1400 and [Obj Account]<1800 then "Inventory" else .. if [Obj Account]>=8600 and [Obj Account]<9300 then "G& A Expenses" else if [Obj Account]>= 9300 then "Financial Epenses" else nullAdd a custom column with the above code in it. I didn't do all 20 lines, just the first 3 and last 2 so you could see the pattern. A few notes:
- M code is case sensitive, so it is if/then/else, not IF, not If. Must all be lower case.
- && isn't necessary. Use the keyword 'and' (lower case)
- Unlike DAX, you must always have the if/then/else condition, so at the end it is 'else null' - you will get an error without that final else
- You don't use table names, just column names. M cannot see any table (special use cases not discussed) outside of the current query you are in, so just use the field name.
3 Replies
M doesn't have a SWITCH function, but for what you are doing, that will be fine.
if [Obj Account]>= 1100 and [Obj Account]<1230 then "Cash & Banks" else if [Obj Account]>= 1230 and [Obj Account]<1400 then "Account Receivables & Others AR" else if [Obj Account]>= 1400 and [Obj Account]<1800 then "Inventory" else .. if [Obj Account]>=8600 and [Obj Account]<9300 then "G& A Expenses" else if [Obj Account]>= 9300 then "Financial Epenses" else nullAdd a custom column with the above code in it. I didn't do all 20 lines, just the first 3 and last 2 so you could see the pattern. A few notes:
- M code is case sensitive, so it is if/then/else, not IF, not If. Must all be lower case.
- && isn't necessary. Use the keyword 'and' (lower case)
- Unlike DAX, you must always have the if/then/else condition, so at the end it is 'else null' - you will get an error without that final else
- You don't use table names, just column names. M cannot see any table (special use cases not discussed) outside of the current query you are in, so just use the field name.
Thank you very much for your help very helpful
