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.
Hello,
I'm currently doing some data cleaning but I stumbled into a problem. I have a column filled with names such as John, Willem, etc. But within that same column, I also have data in the form of E40 or N804. The items starting with E or N followed by a number should be replaced by null so I can fill down the names.
Is there an easy way to accomplish this?
Thank you in advance!
Solved! Go to Solution.
Hi @Jenolski ,
Try this code:
let _value = [Column1] in
if
(
Text.Start([Column1], 1) = "E" or
Text.Start([Column1], 1) = "N"
) and List.Count(List.Select({"0".."9"},
each (_) = Text.Range(_value, 1,1))) > 0 then null
else [Column1]
Additionally, E and N can be followed by different numbers. So not only the examples that I earlier gave.
Hi @Jenolski ,
Try this code:
let _value = [Column1] in
if
(
Text.Start([Column1], 1) = "E" or
Text.Start([Column1], 1) = "N"
) and List.Count(List.Select({"0".."9"},
each (_) = Text.Range(_value, 1,1))) > 0 then null
else [Column1]
Works like a charm, thank you!
Well, we will need to find something that uniquely identifies those rows. Perhaps the presence of a comma (,)?
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |