Selecting only rows with specific key and data

Hi acerNZ 

In your data you have 3 columns, ID, Key and Data.

So [Key] refers to that 2nd column.  

The error 'The field key of the record wasn't found' indicates that your source data doesn't have a column named Key. How did you set up your data? 

To do this filter you don't need to use the Advanced Editor.  In the query editor, just click on the filter at the top of the column, then uncheck everything except Location.

The last part of the query defines what data is the result of the query so 

in
   Custom1

means return the result of the Custom1 step.  You can return anything you want here as the query result, but typically it is the name of the last step before the in keyword. 

Please see attached file(s) (below) for source data and queries.  I've created 1 Excel workbook with the source data set out in a table as you showed in your initial post.  There's a query in that file that does the filter/transformation.

There's also a PBIX file that grabs the data from the same file.  You'll see in the PBIX file that it's looking for the workbook in d:\temp - change that to whatevere folder you put the workbook in.  But hopefully the examples are sufficient to illustrate what is going on.

let
    Source = Excel.Workbook(File.Contents("D:\temp\filter.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ID", type text}, {"Key", type text}, {"Data", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Key] = "Location"))
in
    #"Filtered Rows"

 Phil

PBIX File

Excel Workbook

If I answered your question please mark my post as the solution.

If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Hi PhilipTreacy 

Thank you very much for your time. 

I replicated your solution to problem and it worked. 

Yes you are right, if my rows were less than 1000? I do not see my data in the filter as load more, still it doesn't show up. I do see and can filter in the data modeling view but I am not sure, which one is more of best practice, filter here at query editor using M language you have guided me or Dax at data view. 

Yes, key wasn't there in the column and also I observed and learnt the following, please confirm.

Though I do not need to change the coulmns, I have to create a #Changed Type to have column Key to associate key to the filter 

and then filter key with needed string ? 

Because, I removed this and pointed #"Filtered rows" to Source and it was complaining about Key as unidentified. 

  #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ID", type text}, {"Key", type text}, {"Data", type any}}),

let
    Source = Excel.Workbook(File.Contents("D:\temp\filter.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ID", type text}, {"Key", type text}, {"Data", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Key] = "Location"))
in
    #"Filtered Rows"

Thanks a ton PhilipTreacy  it really worked.