VAR and SWITCH in Create New Column error

[ThomasDay]

Hello,

I'm looking to create my first "new column" using power bi desktop.  I may be off base and using DAX instead of M. In essence I'm taking the last 4 digits of a provider number and creating a Text Category depending on this value.  It is choking at the Temp Variable I set up which it highlights in Blue in the photo below.  For the full statement I used...read below the photo.  So any general steering you folks can provide would be appreciated.  Tom

Here's the expression I'm using:

=

VAR
Temp = [PROVIDER_NUMBER]-((TRUNC ([PROVIDER_NUMBER]/10000))*10000)
RETURN
SWITCH (
TRUE(),
Temp >= 1 && Temp <= 879, "GenAcute",
Temp >= 1300 && Temp <= 1399, "Rural PCH",
Temp >= 3300 && Temp <= 3399, "Childrens",
Temp >= 3400 && Temp <= 3499, "Rural PCH",
Temp >= 3975 && Temp <= 3999, "Rural PCH",
Temp >= 8500 && Temp <= 8899, "Rural PCH",
Temp >= 3300 && Temp <= 3399, "Rural PCH",
"Other")

[austinsense]

You did everything exactly right IF you were creating a calculated column in DAX BUT as you mentioned you wrote this in the query editor which uses the M language.  Copy that formula, load the query and then go to the table and add a calculated column with that exact same logic.

[ThomasDay]

Alright!!  At least the DAX looks right.  I'll take a look at M and see what might do the trick there since I would then never have to think about it again on data refresh.  I'm guessing an intermediate col would be useful and then not load it....to break it into pieces.

Thanks again, and I'll try out the code above just for the satisfaction of it and then crack the M book.  Tom

[ThomasDay]

I really should ask: "do you have any tips for the approach in M?"  Is it to create a temp column and build a big nested IF (presuming there is such a thing in M)?  Thanks for any help in advance.  Tom

[austinsense]

The nested IF statement is probably a good place to start - here's a more complex way to do it LINK but I wouldn't start with this approach.

[ThomasDay]

Austin, the fnSwitch is a really nice learning exercise.  I've not finished, it's a nice view forward into M's capabilities.  I am going to do the Monkey thing and replicate it as a "pattern" piece of code and try it out....and then backfill my understanding of it.  If I can get it to work, that would be a nice resource for my snippets collection.

Thanks,

Tom

[ThomasDay]

One of the difficult things about M is figuring out where to enter code.  I am creating connections only, using power bi, and loading the data model.   When I'm building that query...I'll do a bunch of things, then want to use the switch function while creating a new column.  (There's no intervening conversion of the column I'm assessing for the switch values.)  Where do I put the code?

BTW, The DAX version is:

=
SWITCH (
TRUE(),
[CTRL_TYPE]= "1", "Nonprofit",
[CTRL_TYPE]= "2", "Nonprofit",
[CTRL_TYPE]= "3", "Nonprofit",
[CTRL_TYPE]= "4", "Proprietary",
[CTRL_TYPE]= "5", "Proprietery",
[CTRL_TYPE]= "6", "Proprietery",
[CTRL_TYPE]= "7", "Govt",
[CTRL_TYPE]= "8", "Govt",
[CTRL_TYPE]= "8", "Govt",
[CTRL_TYPE]= "9", "Govt",
[CTRL_TYPE]= "10", "Govt",
[CTRL_TYPE]= "11", "Govt",
[CTRL_TYPE]= "12", "Govt",
[CTRL_TYPE]= "13", "Govt",
"Undefined")