Forum Discussion
Merging two tables with similar values
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.
1 Reply
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])
)
),
