Help splitting/extracting column by character transition based on condition from another column

[rpablo]

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?

[DOLEARY85 · Accepted solution]

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 👍

[rpablo]

Hi  @DOLEARY85 ,

Thank you very much for your help. That worked just as expected.

[DOLEARY85]

No problem, happy to help 🙂