Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Fuzzy matching logic

I have already removed the extensions using this M code in Power Query.   let // Load your Excel file/table Source = Excel.Workbook(File.Contents("C:\Users\mark.jz.yeap\Documents\ATCi - Mark\Matc...
Need Help
Tips and Tricks
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    Thankyou, lbendlin, for your response.

    Hi Mark-JZ-Yeap,

    We appreciate your inquiry through the Microsoft Fabric Community Forum.

    Based on my understanding, you are seeking to match the RawAccount column against a reference company list using fuzzy logic, with varying similarity thresholds depending on the length of each cleaned name. As Power BI’s fuzzy matching in Power Query does not directly support dynamic thresholds for each row, please follow the steps outlined below, which may assist in resolving the issue:

    1. Continue using your suffix-removal M script to cleanse both the RawAccount and reference list.
    2. Add two columns: one for the string length and another to assign a similarity threshold based on that length.
    3. Split your dataset into three categories: Length ≤ 4 → exact match (threshold = 1.0); Length = 5 → fuzzy match (threshold = 0.9); and Length > 5 → fuzzy match (threshold = 0.8).
    4. Perform merges accordingly using Power Query’s fuzzy matching feature.
    5. Recombine the results using Append Queries.

    This method effectively replicates your logic without any manual matching requirements and will scale well.

    Additionally, please refer to the following links for more information:
    How fuzzy matching works in Power Query - Power Query | Microsoft Learn
    Merge queries overview - Power Query | Microsoft Learn

    If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

    Thank you.

lbendlin's avatar
lbendlin
Super User
1 year ago
 I need to match using this logic

What do you need to match?

Anonymous's avatar
Anonymous
Not applicable
1 year ago

For example:

RawAccount

3m inc

7-evelen, inc.

Rolex PLC

Avanaid pte ltd

Deloite pty ltd

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Here is one, slightly brute force, implementation.

     

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMs5VyMxLVorViVYy100tS81JzdMBieiBhYLyc1IrFAJ8nME8x7LEvMTMFIWCklSFnJIUsJhLak5+JpBfUFIJEYsFAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [RawAccount = _t]
  ), 
  Suffixes = List.Buffer(
    {
      " intl pte ltd", 
      " (Private) Limited", 
      " Pte Lte", 
      " (Pte) Ltd", 
      " PTE LTD", 
      "PTE. LTD.", 
      " Pvt. Ltd.", 
      " PTE. LTD", 
      " Pte Ltd", 
      " Pte. Ltd.", 
      " Private Limited", 
      " Public Company Limited", 
      " COMPANY LIMITED", 
      " Co., LTD.", 
      "Co; Ltd", 
      "co.,ltd.", 
      " Co.,LTD", 
      " Co.,Ltd.", 
      " Co.,Ltd", 
      " Co., Ltd.", 
      " Co., Ltd", 
      " CO., LTD.", 
      " Company Limited", 
      " LTD.", 
      " LTDA", 
      " LTD", 
      " Ltd.", 
      " Ltda", 
      " Ltd", 
      ", Inc.", 
      ", Inc", 
      ",INC.", 
      " INC.", 
      " Inc.", 
      " Inc", 
      " INC", 
      " Corporation", 
      " Corporate", 
      " Corp.", 
      " Corp", 
      " Sdn Bhd", 
      " Bhd", 
      " PLC", 
      " Pte", 
      " Limited", 
      " Pty", 
      " limited", 
      " LIMITED", 
      "PTe", 
      " pte ltd", 
      " CO.", 
      " S.A.", 
      " Plc", 
      "CORPORATION", 
      " LLC", 
      " B.V.", 
      ".com", 
      " G.K.", 
      " GmbH", 
      " AE"
    }
  ), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Cleaned", 
    each List.Accumulate(
      {0 .. List.Count(Suffixes) - 1}, 
      [RawAccount], 
      (s, c) =>
        if Text.Lower(Text.End(s, Text.Length(Suffixes{c}))) = Text.Lower(Suffixes{c}) then
          Text.RemoveRange(s, Text.Length(s) - Text.Length(Suffixes{c}), Text.Length(Suffixes{c}))
        else
          s
    ), 
    type text
  )
in
  #"Added Custom"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thank you.

       

      What if you can remove those extensions without using hardcore?