How to Create a Filter To Identify First 3 Text Characters - In Power Query

[mdrammeh]

I have a database with millions of records. Each record is identified by a number and text description. Therefore, I want to create a lookup by the first 3 text characters that begin with "EDT" and rename it as "External" and if it does not begin with "EDT" then call it "Internal". 

Example:

ELM-HEMLOCK 1.6MI 1PH RBLD PITA1328 - This should return "Internal"

EDT:  OREILLY RD BRODERICK RD 1.7MI 1PH OH RBLD WINNECONNE - This should return "External"

Thanks in advance.

[Datatouille]

HI @mdrammeh

I guess you want to perform this task in a calculated column?

You can use Text.Start which is the equivalent of Left function in Excel/DAX.

Create a custom column from Power Query:

if Text.Start([Pays],3) = "EDT" then "External" else "Internal"

Be careful Power Query formula language is case sensitive !

[mdrammeh]

So let's say the Column with the description is titled: "Project Description". How would I rewrite this in Power Query?

if Text.Start([Pays],3) = "EDT" then "External" else "Internal"

[Datatouille]

Sorry "Pays" was just an example.

Put the name of the column which contains your codes or use @Vvelarde method suggested above 🙂

[mdrammeh]

I think am missing something still. Sorry for being such a pain 🙂

[Datatouille · Accepted solution]

No that is totally fine - we are here to help 🙂

You forgot the "if" statement !

If you don't want to code manually, have a look at the method @Vvelarde suggests above ! It uses simple dropdown lists and you are basically generating (M) code without writing it 🙂

[Vvelarde]

@mdrammeh

Hi, in Edit Query--Add Column - Select Conditional Column