How to show only rows where Empid field begins with a letter?

[croberts21]

I have PowerBI v2.98 on Windows 10 Pro.

I'm familiar with databases, a bit of SQL, programming and regex but not PBI.

I'm new to PBI. I'm trying to look at a single table of employee data with a key called EmpiID. This is 8 letters or numbers to uniquely identify the employee. Employee IDs that begin with a number are temp employees. I only want to show full time employees which begin with a letter.

I've done some searching and it does not seem that PBI supporst regex in the Filter pane. So how would I do this? I'd prefer entering a Regex in the Filters pain but PBI doesn't support that. I'm new to PBI functions as well and have never made one.

As an example here are fictional EmplIDs: 9JAVFO, 8STEVSM, ANGPAM, JIMGRE.

The rows I want to show will be EmpIDs that begin with a letter like: ANGPAM, JIMGRE.

Thank you.

p.s. I'm at the very early stages of learning PBI. I haven't gotten to functions or measures or calculated columns yet. I have to learn anything online or via a book or video. Most videos, I'm finding, use an older version of PBI with different menu options so I cannot use that video.

[Greg_Deckler · Accepted solution]

@croberts21 Try:

Measure =
  VAR __Numbers = { 0,1,2,3,4,5,6,7,8,9 }
  VAR __FirstLetter = LEFT(MAX('Table'[EmpIDs]),1)
RETURN
  IF(__FirstLetter IN __Numbers,0,1)

[goncalogeraldes]

Hello there @croberts21 ! You can try a work around solution which is to use Power Query to cerate a custom column and filter your report with this column. You can add a "Custom Column" in Power Query and use the following code in the formula bar:

= Table.AddColumn(#Your Previous Step, "Is_Temp", each Value.Is( Value.FromText( Text.Start([EmpiID],1) ), type number))

You can the use the TRUE/FALSE to filter your data for temporary workers.

You can also do this with a measure, like so:

Is_Temp = 
var _select = SELECTEDVALUE(Table[EmpiID])

return
IF(
	(LEFT(_select, 1)) in {"1","2","3","4","5","6","7","8","9","0"},
	TRUE,
	FALSE
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[mahoneypat · Accepted solution]

I was working on this at same time.  Here is another way to do it.

StartsWithLetter = var thisID = MIN(EmpIDs[EmpID])

var firstchar = LEFT(thisID, 1)

return IF(IFerror(VALUE(firstchar), -1) >-1, "N", "Y")

Pat

[croberts21]

This one worked for me. Thanks! I'm adding it to my notes.

[croberts21]

Will this custom column stay only in this .pbix file? So I have to add it to any other file I use?

[croberts21]

Ok. I had to remove the MIN function from the first line so now I'm half way there. I just have to apply my filter to this custom column.

Is "Custom Column" the right term for PBI in your example? I want to get the terms right.

Ok, it works! Thank you!

[croberts21]

Ah, your Value() function checks to see if the first letter is a digit or not. Yes? So if Value() returns 0 or more then the first letter is an number. That makes sense since 0 is a number also.

[Greg_Deckler · Accepted solution]

@croberts21 Try:

Measure =
  VAR __Numbers = { 0,1,2,3,4,5,6,7,8,9 }
  VAR __FirstLetter = LEFT(MAX('Table'[EmpIDs]),1)
RETURN
  IF(__FirstLetter IN __Numbers,0,1)

[croberts21]

I will have to search on how to do Measures. I entered yours in, and it just disappears, and I don't get a new column. I don't even know where it goes so I can edit or view it again.

Please have patience. I did revise my original post to show how new I am go PBI.

[croberts21]

I'm familiar with databases and programming but not with PBI. Is that first variable a set? Is "set" the correct PBI term? I wouldn't know where to enter this code. I was able to the employee table and show only active employees. That's my skillset with PBI.

[Greg_Deckler]

@croberts21 It's a table constructor so it creates a 1 column table. I wrote a Measure so you would right-click a table in your Fields pane and choose new measure. It was written as what I call a "complex selector" that you can use in the Filters pane. I like @mahoneypat's idea as well of a calculated column. Same process, right-click a table in the Fields pane and choose new calculated column. The column version of my code is:

Column =
  VAR __Numbers = { 0,1,2,3,4,5,6,7,8,9 }
  VAR __FirstLetter = LEFT([EmpIDs],1)
RETURN
  IF(__FirstLetter IN __Numbers,0,1)