Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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
Hi @Anonymous .
Hope this article can help you.
https://www.sqlbi.com/articles/checklist-for-memory-optimizations-in-powerpivot-and-tabular-models/
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...
@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!
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.