Forum Discussion
Help splitting/extracting column by character transition based on condition from another column
Hi all,
I need your guidance on how to split/extract a column by character transition if a condition is true.
I have a table with two columns:
| Hostname | Username |
| Machine01 | Username01 |
| Machine02 | Username02 |
| Cloud Machine | v12345 |
| Cloud Machine | v67890 |
| Machine03 | Username03 |
| Cloud Machine | v91735 |
My condition must be: IF [Hostname] = "Cloud Machine" THEN split username from non-digit to digit ELSE [Username]
Therefore, my new column must be:
| Hostname | New Username |
| Machine01 | Username01 |
| Machine02 | Username02 |
| Cloud Machine | 12345 |
| Cloud Machine | 67890 |
| Machine03 | Username03 |
| Cloud Machine | 91735 |
Any ideas on how to do this either in DAX or PowerQuery?
Hi,
try this in a new column in power query:
if [Hostname] = "Cloud Machine" then Text.Remove(
[Username],
{
Character.FromNumber(32)..Character.FromNumber(47),
Character.FromNumber(58)..Character.FromNumber(255)
}
)
else [Username]If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
3 Replies
Hi,
try this in a new column in power query:
if [Hostname] = "Cloud Machine" then Text.Remove(
[Username],
{
Character.FromNumber(32)..Character.FromNumber(47),
Character.FromNumber(58)..Character.FromNumber(255)
}
)
else [Username]If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
