Forum Discussion

MatM's avatar
MatM
Microsoft Employee
4 years ago
Solved

Filtering based on prefix match

I have two tables.

 

Table1 -- main data

PathInteresting dataInteresting data
/uk/london/central/bank... 
/uk/london/central/mayfair  
/uk/london/central/mayfair  
/uk/london/west  
/uk/cambridge  
etc.  

 

Table2 -- scopes of interest and corresponding paths

ScopePath
London Central/uk/london/central
London West/uk/london/west
Cambridge/uk/cambirdge

 

I want to build a dashboard that has a list of scopes at the top, and when the user selects a scope, all other charts on the dashboard are filtered so that they only take account of data in Table1 where the row has a path that is a descendant of (that is, starts with) the selected scope's path (from Table 2). 

 

Can anyone help me please?   

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.

     

4 Replies

  • 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.