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 using Tabular 1400 for the data model.
I'm trying to merge two tables on values that do not 100% match. One table has a segment of the string from that other table I'm trying to merge.
I tried using fuzzy but we're talking over 100 million rows, not sure if it's the best for performance.
The goal is to normalize values in the main table by finding key substring within those values. I hope this makes sense.
Use Table.AddColumn with a custom columngenerator function. It's not lightning fast but has acceptable performance.
https://docs.microsoft.com/en-us/powerquery-m/table-addcolumn
Here is the general structure
let
Source = Table.AddColumn(Table, "NewColumn",
(parameter) => function
),…
and here is an example for a fuzzy merge.
let
Source = Table.AddColumn(Assignments, "Match",
(Earlier) =>
Table.SelectRows(#"Keys",
each (Earlier[LocID]="*" or Earlier[LocID]=[LocID])
and (Earlier[PSA]="*" or Earlier[PSA]=[PSA])
and (Earlier[ST ID]="*" or Earlier[ST ID]=[ST ID])
)
),
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.