Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
How do I clean the data like the figure below? I didn't find a similar function in the PQ interface.
Thanks!
Solved! Go to Solution.
Hi, @Anonymous ,
You can use Table.AddFuzzyClusterColumn() ,
@Daniil has a great blog post for that.
Best.
Mark this post as solution if this helps.
Hi, @Anonymous , you might want to try the following code,
let
typo = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk/NycxLL8nPU4rVAfOg3GIUfiZCOh9FNYwXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t]),
misspelling = Table.FromList({"Welington City","CityWellingtons","City Wellingtin 443","Nr. 22 WellongtonCity"}, Splitter.SplitByNothing(), {"Addr"}),
#"Added Custom" = Table.AddColumn(misspelling, "Custom", each
List.Accumulate(
typo[City], _[Addr], (s, c) => Text.Replace(s, c, "Wellington")
)
)
in
#"Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @Anonymous
Alternatively, look for a commonality in the text and then create a Conditional Column
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if Text.Contains([City], "lingto", Comparer.OrdinalIgnoreCase) then "Wellington" else null)
in
#"Added Conditional Column"
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Hi, @Anonymous ,
You can use Table.AddFuzzyClusterColumn() ,
@Daniil has a great blog post for that.
Best.
Mark this post as solution if this helps.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
12 | |
11 |