power bi switch is unrecognized in query

Question

Have used the following code to create a custom column but its stuck at the an expression error that it is unable to recognize the SWITCH function :

= Table.AddColumn(#"Removed Columns", "Empolyees", each SWITCH([Index],  
1, Empolyees = "Chris1",   
2, Empolyees = "Chris2",
3, Empolyees = "Chris3",
4, Empolyees = "Chris4", 
5, Empolyees = "Chris5",
6, Empolyees = "Chris6",
7, Empolyees = "Chris7",
8, Empolyees = "Chris8",
BLANK()
))

I have tried removing the quotations, changing the column names but all to no avail.Please Advice. Thanks in Advance!

score 5 · Accepted Answer · answered Apr 09 '18 at 09:11

You've mixed up M and DAX. They are two different languages, and both are used in Power BI. SWITCH() is a DAX function, hence it cannot be used in the M query that you're writing.

You can replace the SWITCH logic with an if-then-else expression in M:

= Table.AddColumn(#"Removed Columns", "Employees", each if [Index] = 1 then "Chris1" else if [Index] = 2 then "Chris2" else if [Index] = 3 then "Chris3" else if [Index] = 4 then "Chris4" else if [Index] = 5 then "Chris5" else if [Index] = 6 then "Chris6" else if [Index] = 7 then "Chris7" else if [Index] = 8 then "Chris8" else "")

Depends on exactly what you want to achieve, other functions can be used, but I'll keep it this way for now instead of making assumptions.

Hi @clement-ong does this help solving the issue after all? If so, you can [accept an answer](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work/5235#5235) to positively close the question. — Foxan Ng, Jun 25 '18 at 06:03

score 1 · Answer 2 · answered Apr 27 '18 at 07:59

It would be much better to store the list of Employees in a table, and merge that with your query. You can generate a table within the query - as an example:

let
    Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
    TempTable = #table(
        {"ID","Name"},{
            {1,"Employee 1"},
            {2,"Employee 2"},
            {3,"Employee 3"},
            {4,"Employee 4"},
            {5,"Employee 5"}
            }
        ),
    #"Merged Queries" = Table.NestedJoin(Source,{"ID"},TempTable,{"ID"},"Join",JoinKind.LeftOuter),
    #"Expanded Join" = Table.ExpandTableColumn(#"Merged Queries", "Join", {"Name"}, {"Name"})
in
    #"Expanded Join"

Better practice would be to store the Employee ID / Names in a separate table, and join in the same way.

power bi switch is unrecognized in query

2 Answers2