How to Convert DAX Code to Power Query M language

[Mohammed-]

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")

[mussaenda]

Hi @Mohammed- ,

Have you tried the suggested solution below?

[edhans · Accepted solution]

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
null

Add 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.

[Mohammed-]

Thank you very much for your help very helpful