Forum Discussion

MatM's avatar
MatM
Microsoft Employee
4 years ago
Solved

Filtering based on prefix match

I have two tables.   Table1 -- main data Path Interesting data Interesting data /uk/london/central/bank ...   /uk/london/central/mayfair     /uk/london/central/mayfair     /...
Need Help
  • KNP's avatar
    KNP
    4 years ago

    Hi MatM,

     

    You could use the 'fuzzy merge' option to match on Path. You may need a little trial and error to get the settings just right, but in your sample data, it worked with the defaults.

     

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W0i/N1s/Jz0vJz9NPTs0rKUrM0U9KzMtW0lHS09MDkgpKsTpYVeUmVqYlZhaBlZCvrDy1uARDLjkxN6koMyU9FSETCwA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Path = _t, #"Interesting data" = _t, #"Interesting data.1" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Path", type text}, {"Interesting data", type text}, {"Interesting data.1", type text}}
  ),
  #"Merged Queries" = Table.FuzzyNestedJoin(
    #"Changed Type",
    {"Path"},
    TableB,
    {"Path"},
    "TableB",
    JoinKind.LeftOuter,
    [IgnoreCase = true, IgnoreSpace = true]
  ),
  #"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"Scope"}, {"Scope"})
in
  #"Expanded TableB"

     

    PBIX example attached for reference.

    Hope this helps.

     

KNP's avatar
KNP
Super User
4 years ago

Hi MatM,

 

You could use the 'fuzzy merge' option to match on Path. You may need a little trial and error to get the settings just right, but in your sample data, it worked with the defaults.

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W0i/N1s/Jz0vJz9NPTs0rKUrM0U9KzMtW0lHS09MDkgpKsTpYVeUmVqYlZhaBlZCvrDy1uARDLjkxN6koMyU9FSETCwA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Path = _t, #"Interesting data" = _t, #"Interesting data.1" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Path", type text}, {"Interesting data", type text}, {"Interesting data.1", type text}}
  ),
  #"Merged Queries" = Table.FuzzyNestedJoin(
    #"Changed Type",
    {"Path"},
    TableB,
    {"Path"},
    "TableB",
    JoinKind.LeftOuter,
    [IgnoreCase = true, IgnoreSpace = true]
  ),
  #"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"Scope"}, {"Scope"})
in
  #"Expanded TableB"

 

PBIX example attached for reference.

Hope this helps.

 

MatM's avatar
MatM
Microsoft Employee
4 years ago

Nice!   Thank you.  

 

I'm very new to this though and one thing is unclear...  The output of the fuzzy match seems to leave some kind of table reference on each row of the source table which then needs to be expanded via ExpandTableColumn?   The example in the FuzzyJoin documentation doesn't seem to need that step?  Table.FuzzyJoin - PowerQuery M | Microsoft Docs

  • KNP's avatar
    KNP
    Super User
    4 years ago

    The Table.FuzzyJoin does skip that step, kind of. It's not ideal if you only wanted one column from the other table and won't work if the two tables share any common column names.

     

    The Table.FuzzyNestedJoin does result in a table which then allows you to select the columns you want to expand. 

     

    In your example, the FuzzyJoin will error because of the same column name (Path) in the two tables.