Using values from two tables to create a calculated DAX column with Switch()/IN() statement

[Semipro211]

Good Afternoon,

I am new PBI community member but I have been working with Power BI for a couple years at work. I have finally run into something that I could not find an answer to via searching the forums. My problem is as follows:

I have a large number of computer devices throughout my organization, ~15,000. The names of these machines reflect what region they are in (we have 147 regions). What I am attempting to do is create a column that looks at the machine name and uses something like a switch statement to give a result of the region code. The challenge is that sometimes, due to some previous naming conventions, the machine name may contain a numeric code instead of the alpha code for the region, so I am trying to see if I can basically store the values from a column in one table and use those inside of a formula to calculate the new column.

Here are some examples of what I have and what I am trying to get. In Table1, the "Region" is what I am trying to get with the forumula.

Table1

Table1

ComputerName	Region (Computed Column)
WLABCD1234	ABCD
ELDEFG4567	DEFG
26381WLTE	GHIK

Table2

RegionNumber	RegionCode
1234	ABCD
4567	DEFG
26381	GHIK

What I am trying to do with the formula is look at ComputerName in Table1 and if it finds a letter sequence that matches a RegionCode in Table2, it puts that code in Region Column, otherwise if a numerical sequence matches RegionNumber, use that to get the value of RegionCode and add that to the column. I am not against using multiple columns if needed, but my current method would use a Switch statement with over 500 cases, and I was hoping there was a more efficient way of doing it. Here is the formula I have so far, thanks to @v-yuta-msft in a previous post.

Region =
SWITCH(
     TRUE(),
     ISERROR( FIND( "ABCD", 'Table1'[ComputerName])) <> TRUE(), "ABCD",
     ISERROR( FIND( "1234", 'Table1'[ComputerName])) <> TRUE(), "ABCD",
     ISERROR( FIND( "DEFG", 'Table1'[ComputerName])) <> TRUE(), "DEFG",
     ISERROR( FIND( "4567", 'Table1'[ComputerName])) <> TRUE(), "DEFG",
     ISERROR( FIND( "GHIK", 'Table1'[ComputerName])) <> TRUE(), "GHIK",
     ISERROR( FIND( "26381", 'Table1'[ComputerName])) <> TRUE(), "GHIK",
...

As you can see, doing this for every value I have in Table2 would create a very long SWITCH() formula. I was hoping to find something like I have done in PowerShell where I can store or read the values in Table2 and use an IN ('Table1'[ComputerName]) or something similar.

Any help is very much appreciated!

[Mariusz]

Hi @Semipro211 

Please see the attached file with a solution in Power Query 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Semipro211]

Hi @Mariusz 

Thank you for taking the time to respond. I am surprised how simple the M code is to do this! I did run into one issue though. When I attempt to duplicate this code in my own pbix project, my expanded custom column gives me null values. Due to the sensitive nature of the actual information I am working with, I cannot post my pbix file, but I can at least give the structure of the two real tables with their actual column names and some dummy data. All columns are text columns.

AD Complete

CN

TWWSOV00985231N

LWYKTC21443004L

EXANNZ20187333M

NCOM20071133W

CLC_CSI

CLC	CSI
985	WSOV
21443	YKTC
20187	ANNZ
20071	ANNZ

let
  (ommitted for brevity)
  #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        Table.SelectRows( 
            CLC_CSI, (b) => Text.Contains( [CN], b[CLC] ) 
        )
    ),
  #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"CSI"}, {"CSI"})
in
    #"Expanded Custom"

I don't get any errors, but every value is null.

[Semipro211]

@Mariusz  Allow me to correct myself, I must have misinterpretted part of the M code in your solution, when I changed b[CLC] to b[CSI], the column now works as expected/hoped.

THANK YOU!