Filtering based on prefix match

[MatM]

I have two tables.

Table1 -- main data

Path	Interesting data	Interesting 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

Scope	Path
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?   

[v-yalanwu-msft]

Hi,  @MatM ;

You could use custom visual -text filter .

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381309?tab=Overview

Or Smart Filter Pro by OKViz;

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA200001464?tab=Overview

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KNP · Accepted solution]

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]

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]

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.