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.
I am looking for the best approach to Trim the First letter of each text string to come up with an abbreviation.
Example.
Supply Chain and Mgmt. = SCM
Electric Light & Power = ELP
Joe's Power Source Company = JPSC
What functions should I use? This is what I have so far, however, it is asking for a paran and =0, or =1,
IF(len([Org])-len(SUBSTITUTE([Org]," ",""))=0,left([Org],1), if(len([Org])-len(SUBSTITUTE([Org]," ",""))=1, left([Org],1) &mid([Org],find(" ",[Org])+1,1),left([Org],1)&mid([Org],find(" ",[Org])+1,1)&mid([Org],find(" ",[Org],find(" ",[Org])+1)+1,1)))
Thanks,
Karen
Solved! Go to Solution.
Hi @kfschaefer,
Based on my test, you could refer to below steps:
Sample data:
Split the [Text] column by space in query editor:
Extracted First Characters:
Apply it and create a calculated column:
Column = var a=IF(UNICODE('Table1'[Text.1])>=65&&UNICODE('Table1'[Text.1])<=90,[Text.1],BLANK()) var b=IF(UNICODE('Table1'[Text.2])>=65&&UNICODE('Table1'[Text.2])<=90,[Text.2],BLANK()) var c=IF(UNICODE('Table1'[Text.3])>=65&&UNICODE('Table1'[Text.3])<=90,[Text.3],BLANK()) var d=IF(UNICODE('Table1'[Text.4])>=65&&UNICODE('Table1'[Text.4])<=90,[Text.4],BLANK()) return CONCATENATE(a,CONCATENATE(b,CONCATENATE(c,d)))
Result:
You could also download the pbix to have a view:
Regards,
Daniel He
Hi @kfschaefer
An alternative, and more human-readable, solution using Power Query M is a single line of code (nested for readability):
Text.Remove(
Text.Replace(
Text.Proper(
Text.Remove(
Text.Trim(
Text.Clean([Org])
),
{"'"}) //remove those characters interpreted as delimiters in Text.Proper()
),
" And "," "),
{"a".."z","0".."9","(",")"," ",",",".","&","£"})
The Power of M lies in it's human readability, which not only does not require encyclopaedic knowledge of ASCII or Unicode, but also automatically makes it much easier to customise -- all data sets are wonky in different and creative ways.
Your source data will drive the functions you use and the order in which you use them. Some of the steps in this example can be reordered or repeated to suit your data source.
Read more here: The Power of M... and don't forget the function reference library
BEK
redefining the box
Hi @kfschaefer,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Hi @kfschaefer,
Based on my test, you could refer to below steps:
Sample data:
Split the [Text] column by space in query editor:
Extracted First Characters:
Apply it and create a calculated column:
Column = var a=IF(UNICODE('Table1'[Text.1])>=65&&UNICODE('Table1'[Text.1])<=90,[Text.1],BLANK()) var b=IF(UNICODE('Table1'[Text.2])>=65&&UNICODE('Table1'[Text.2])<=90,[Text.2],BLANK()) var c=IF(UNICODE('Table1'[Text.3])>=65&&UNICODE('Table1'[Text.3])<=90,[Text.3],BLANK()) var d=IF(UNICODE('Table1'[Text.4])>=65&&UNICODE('Table1'[Text.4])<=90,[Text.4],BLANK()) return CONCATENATE(a,CONCATENATE(b,CONCATENATE(c,d)))
Result:
You could also download the pbix to have a view:
Regards,
Daniel He
In the table, if the name or the text string you want to format is in one column, I could think of one work around where you could extract the text before or after the delimiter/s. This will create separate columns for each word in the string. Then extract the first character for each word. Finally, combine the extracted first letters. It may seem like a long process, but it is actually easy to achieve.
Hope this helps!
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.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |