Selecting only rows with specific key and data

[acerNZ]

Hi Experts

I need to only pull all the row information for only those rows filtered for location. 

of course, my in my table, the key and data runs into thousands for each ID and not consistent /ordered  or same keys for all IDs. But I need to pull specific keys out. 

This I tried and did not work no change in the table.

In the advanced editor of power query, I have appended the following and it was ok by syntax but did not work 

Myrows = Table.SelectRows(
                each Text.Contains("Location"))

Data Input
ID	Key	Data
ID1	Location	New York
ID1	DOB	05/23/78
ID1	Mobile	001-123-12323
ID2	Location	Denver
ID3	DOB	06/30/80
ID3	Mobile	NULL
ID3	Location	New York
Output
ID1	Location	New York
ID2	Location	Denver
ID3	Location	New York

appreciate your help.

[PhilipTreacy · Accepted solution]

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.

[shaowu459]

Hi, @acerNZ 

Try this in advance editor:

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    Custom1 = Table.SelectRows(Source,each Text.Contains([Key],"Location"))
in
    Custom1

Source is the table you uploaded to PQ editor, Custom1 is a step name, which filters specific key.

[acerNZ]

Hi @shaowu459 

Thank you firstly. 

I think we need to define "Key" ? 

 Custom1 = Table.SelectRows(Source,each Text.Contains([Key],"Location"))
in
    Custom1

I am getting key error and I see in other threads, they define, "#filter", #"group", "#key" etc I wish I know what they are .. 

Please can you let me know how to define [Key] as defined in the formula. 

Also I see that IN has to be same as custom ? 

 

[PhilipTreacy · Accepted solution]

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.

[acerNZ · Accepted solution]

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.