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,
I'm a new user and just playing with a datatable, trying to clean up a text field before publish to users.
The field is a free text in our system. However in some lines it included a system code which takes up space and no use to anyone.
The code has a consistent format, 8-4-4-4-12 5 sets of letter or number ending with ";".
Here is a example:
24f1ef97-5df5-4d3f-8976-eeca0e157432;Creative Production,Production
I've searched the forum and google, couldn't find any similar issues before.
Could anyone provide some help?
p.s. I'm using the Query function inside the Excel.
Thanks,
Lifan
Solved! Go to Solution.
Hi @Lifan
No problem when you have created the additional column (Column1), when it does not find anything after the delimiter it should then have a result of null
So then what you can do is to create a conditional column where if the Additional Column (Column1) is null then use the Original column.
In doing that you will get the output you require.
Hi there, can you give an example of what you would like the desired output to be?
Hi Guavaq,
Good point, I'd just like to remove that weird code and keep the remaining info.
In that example, it should only show:
Creative Production,Production
Thanks!
Lifan
Hi @Lifan
When using the Query Editor, I would suggest the following:
Hi Guavaq,
Thanks but problem is the content doesn't always have the code in there... sorry if didn't make it clear earlier.
It's like this:
24f1ef97-5df5-4d3f-8976-eeca0e157432;Creative Production,Production
ABC promotion
Production charge
245B3897-3ef5-4d9f-5184-ebe5015e7432;7/5,6/7 active
need to change to this:
Creative Production,Production
ABC promotion
Production charge
7/5,6/7 active
Hi @Lifan
No problem when you have created the additional column (Column1), when it does not find anything after the delimiter it should then have a result of null
So then what you can do is to create a conditional column where if the Additional Column (Column1) is null then use the Original column.
In doing that you will get the output you require.
Maybe split to rows using the ";" char in Power Query and filter out any rows that contain a "-"
Yes it worked! Thank you!!!!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |