Forum Discussion

acerNZ's avatar
acerNZ
Helper III
5 years ago
Solved

Selecting only rows with specific key and data

  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 consiste...
Need Help
Tutorial Requests
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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's avatar
    acerNZ
    5 years ago

    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.

shaowu459's avatar
shaowu459
Resolver II
5 years ago

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's avatar
    acerNZ
    Helper III
    5 years ago

    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's avatar
      PhilipTreacy
      Super User
      5 years ago

      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's avatar
        acerNZ
        Helper III
        5 years ago

        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.