CODEWITHSUNDEEP
powerbidaxmpowerbi-datasourcepowerbi-desktop
Cosq
Cosq

Reputation: 155

power bi switch is unrecognized in query

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!

Upvotes: 2

Views: 7294

Answers (2)

Olly
Olly

Reputation: 7891

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.

Upvotes: 1

Foxan Ng
Foxan Ng

Reputation: 7151

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

result

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.

Upvotes: 5

Related Questions