Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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!
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 "")
                Hi @clement-ong does this help solving the issue after all? If so, you can accept an answer to positively close the question.
– Foxan Ng
                Jun 25, 2018 at 6:03
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:
    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"})
    #"Expanded Join"
Better practice would be to store the Employee ID / Names in a separate table, and join in the same way.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.