Merging two tables with similar values

[arzukari]

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.

[lbendlin]

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])

                )

    ),