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 All
I have below raw data :-
Company
#123
SMC
ADL
TDS Technology Pte Ltd
My Expected result :-
Company,Flag
@abc,Funny Charater
#123,Funny Charater
SMC,Signle word Company name
ADL,Signle word Company name
TDS Technology Pte Ltd,Not Issue
Hope some one can advise me.
Paul Yeo
Solved! Go to Solution.
Thanks, @HotChilli for your clarification and kind words 🙂
@Paulyeo11
You need to have two tables, one for the Signs @,# etc and the other for the single word company names :
Then, you will and thy following column to your table to have the desired results.
Flag =
VAR _SignFlag = LEFT([Company],1) IN VALUES(SignsList[Sign])
VAR _Space = CONTAINSSTRING(TRIM([Company])," ")
VAR _SingleWord = NOT(_Space) && NOT(_SignFlag)
VAR _Sign = NOT(_Space) && _SignFlag
VAR _FullName = _Space && NOT(_SignFlag)
RETURN
SWITCH( TRUE(),
_Sign , LOOKUPVALUE(SignsList[Character],SignsList[Sign],LEFT([Company],1)),
_SingleWord , LOOKUPVALUE(CompanyList[Full Name],CompanyList[Word],[Company]),
_FullName, [Company]
)
You can download the file: HERE
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks, @HotChilli for your clarification and kind words 🙂
@Paulyeo11
You need to have two tables, one for the Signs @,# etc and the other for the single word company names :
Then, you will and thy following column to your table to have the desired results.
Flag =
VAR _SignFlag = LEFT([Company],1) IN VALUES(SignsList[Sign])
VAR _Space = CONTAINSSTRING(TRIM([Company])," ")
VAR _SingleWord = NOT(_Space) && NOT(_SignFlag)
VAR _Sign = NOT(_Space) && _SignFlag
VAR _FullName = _Space && NOT(_SignFlag)
RETURN
SWITCH( TRUE(),
_Sign , LOOKUPVALUE(SignsList[Character],SignsList[Sign],LEFT([Company],1)),
_SingleWord , LOOKUPVALUE(CompanyList[Full Name],CompanyList[Word],[Company]),
_FullName, [Company]
)
You can download the file: HERE
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Wow you are the expert . Thank
@Paulyeo11
If you have a handful of companies, you can try the Window emojis by adding a column ( WIN + . ) like
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy you are a great contributor to the forum. I loved your answer on this one, however I think the OP is trying to flag up if a "funny character" is contained already in the string.
@Paulyeo11 you are going to have to define a list of funny characters (or make a rule that anything that is not a letter or number is a "funny character"). I've had a quick play in Power Query by adding a column like this
if not List.ContainsAny(Text.ToList([Column1]), {"a".."b"}) then 1 else 0)
so it could be something similar
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |