Extract exact text starting with a particular text and 32 characters from a column text into new col

[Anonymous]

Hi Community,

I have a column with alphanumeric text in it. I want to extract only the values that start with 'BIT-' and the ones which have a total of 32 characters only as highlighted in green in the data below:

Doc Number	Length
BIT-304-650-UPG-REP-630-000-3001.	33
BIT-222-242-UPG-ADG-380-000-0000_DRAWING SCHEDULE NOTES & LOCALITY PLAN	71
CP40_5114713_3_19_B	19
BIT-491-200-UPG-WTL-280-000-0039.	33
BIT-705-004-UPG-ADG-130-000-0001-	33
5118842 DRAWING_LIST_AND_DESCRIPTIONS_08052019	46
BIT-202-400-SHD-JSM-410-020-2048 - 1	36
ETTL-TAN-1005	13
BIT-401-200-CMM-BOG-300-000-0001(A)	35
BIT-202-475-UPG-REP-400-001-1017 - STANDARD -	45
BIT-BOG-MN-LET-100-0010	23
BIT-100-000-PRO-REP-100-000-0006-NATIVE	39
BIT-CS-MPL-100-0002 Example Interface & Stakeholder Meeting Schedule	68
BIT-CS-MPL-100-0002 Interface Management Plan cover letter	58
BIT-CU-MPL-100-0001 Construction Communications and Engagement Management Plan cover letter	91
BIT-ED-MPL-100-0001 Appendix K	30
BIT-ED-MPL-100-0001 Appendix K - ETTL DMP	41
BIT-ED-MPL-100-0001 Appendix L	30
BIT-ED-MPL-100-0001 Appendix L Produce and Approve Design (Utility Infrastructure) Procedure	92
BIT-ED-MPL-100-0001 Design Management Plan cover letter	55
BIT-MN-LET-100-0001	19
BIT-490-600-UPG-TTW-630-000-3401	32
BIT-403-405-UPG-REP-410-071-0151	32
BIT-302-542-UPG-NMA-550-000-2014	32
BIT-200-000-REP-BOG-460-000-0002	32
BIT-303-600-PAE-BOG-600-000-0001	32
BIT-303-505-CER-ARC-420-000-0001	32
BIT-303-505-CER-ARS-420-000-0001	32
BIT-303-505-CER-TRA-420-000-0001	32
BIT-290-204-SOW-BOG-490-000-0001	32
BIT-100-000-MIN-BOG-150-000-0003	32
BIT-200-000-MIN-BOG-100-000-0035	32
BIT-100-000-MIN-BOG-130-000-0024	32
BIT-200-000-AGN-BOG-130-000-0029	32
BIT-100-000-SKT-BOG-490-000-0002	32
BIT-404-405-SHD-HTI-410-381-3263	32
BIT-100-000-CKT-BOG-111-000-0032	32
BIT-500-000-PRC-BOG-111-000-0003	32
BIT-500-728-MAN-BOG-715-000-0001	32
BIT-500-728-SWO-BOG-715-000-0001	32
BIT-404-210-CPR-BOG-900-000-0002	32
BIT-100-000-CHT-BOG-190-000-0015	32
BIT-401-411-SHD-ZPM-410-005-0202	32
BIT-100-068-CMM-EXT-140-000-0031	32
BIT-303-505-CMM-EXT-420-SLA-0001	32
BIT-100-000-CKT-BOG-700-000-0002	32
BIT-100-000-ITP-BOG-100-133-0001	32

I was looking to do it in Power Query using M language rather than DAX in a New Column as been suggested by Patrick @ Guy in a Cube but open to any suggestions.

I tried to apply filters but can only filter 'BIT-' and found it hard to proceed further. Tried looking up for syntax in M Language guide but in vain.

Can anyone please help me with the code for this new Custom Column which will have only the text highlighted in green?

Any help/guidance is greatly appreciated.

Thanks for your time in advance.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculated table to work on it.

Table 2 = CALCULATETABLE('Table',FILTER('Table',LEFT('Table'[Doc Number],3) = "BIT" && LEN('Table'[Doc Number])=32))

For more details, please check the pbix as attached.

[Anonymous]

Thanks a million @v-frfei-msft . Greatly appreciated.. 😊

Just out of curiosity, I wanted to find out if there are any side-effects/adverse impacts of using CALCULATETABLE on the System Memory, CPU, etc? I tried looking for it on google but did not find anything.

It would be great if you can please shed some light on this.

Thanks & regards, Chandu

[v-frfei-msft]

Hi @Anonymous .

Hope this article can help you.

https://www.sqlbi.com/articles/checklist-for-memory-optimizations-in-powerpivot-and-tabular-models/

[AilleryO]

Hi,

Your first question was about the length of your text, M code for that would be :

= Table.AddColumn(#"Type modifié", "Longueur", each Text.Length([Catégorie]), Int64.Type)

To est if the 3 first letters are BIT, you can use :

To extract the 3 first letters :

= Table.AddColumn(#"Longueur du texte insérée", "Premiers caractères", each Text.Start([Catégorie], 3), type text)

And the test would be something like this :

= Table.AddColumn(#"Premiers caractères insérés", "TEST_BIT", each if [Premiers caractères] = "BIT" then "YES" else "NO")

 Or you can concatenate the 3 first letters and the length and then test BIT32...

To clean your unusefull calculated columns by the end of your query, use the :

Table.RemoveColumns(PreviousLineName, {"Col1_Name","Col2_Name","Col3_Name"})

 Hope it helps, let us know...

[Anonymous]

@AilleryOThanks for your prompt reply.

Sorry I couldn't mark these "Modified type", "Length", [Category], "Length of inserted text", "First characters", etc in your proposed solution!

Just so to make clear of my requirements, I have many columns in my Documents table of which 'DocumentNumber' is one of them (as per the below pic).

I do not have any 'Length' column but I have created one in the original post only to show that I am interested to extract all the 'Document Numbers' that:

1. Start with BIT-

and

2. Length of the Characters must be 32 only (nothing lesser or greater) including BIT-.

Can you please make use of only 'Doc Number' column from my original post and share screenshots of how you are getting to the solution in a step-by-step manner?

Thanks heaps!

[AilleryO]

Hi @Anonymous ,

With a name like yours 😉  I'm sure you can make it with just a few steps :

1/ Use Power Query and the dedicated button (Extract under the Transform tab and choose Length) to create a Column, with the length of your string. That will be useful later for test.

2/ Create a second column, using the same button (Extract), but this time choose, First caracters, and type in 3

3/ Create a new column, using Conditionnal column button, and test if the length is 32 and if the 3 first letters are BIT. Populate your column with 1 and 0 or Yes and No depends on your needs.

4/ Before executing your query, you can remove (using right click) the intermediate columns created for the test.

Or you can as well, create a new table as suggested by CST.

Have a nice day

[Anonymous]

Hi @AilleryO I guess, that's not a valid name just in this case.. 😉

Once again, thanks for your quick response. I still tripped in following your instructions.

Although my preference was to follow your steps and do the coding in Power Query (M) instead of DAX, I had to take the direction of DAX, in this case, running short on time.

Thanks & regards, C