Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Using Keylists with existing queries???

Hi All

 

I have a query about Keylists, I have used them fine in the past although they can be slow to reload - I was wondering if there is another way ??

the first part of the query highlights the jobs I wish to look up in other tables (these can contain up to 10m lines of data)

I would like to do this for multiple tables and I was wondering if this is the wrong way to pull the detail in - would it be better to create a query pulling in the keylist then reference this on each table ?? although I am not sure how to reference this .......

I hope this makes sense

Thanks

 

My query is as follows:

let
    Source = Odbc.Query("***=**********",
    "Select
    #(lf)CONCAT(a.job_no,'-',a.job_rfa_no) AS job_rfa
    #(lf)FROM job_open.job_visit a
    #(lf)WHERE a.acty_cmpnent_vst_date >= ""2018-01-01"""),

    #"Added Custom" = Table.AddColumn(Source, "job_rfa_keylist", each "'"&[job_rfa]&"'"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"job_rfa"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),

    keylist= Text.Combine(#"Removed Duplicates"[job_rfa_keylist],","),

    Source1 = Odbc.Query("***=**********",
    "Select
    #(lf)CONCAT(a.job_no,'-',a.job_rfa_no) AS job_rfa,
    #(lf)a.contract_ind,
    #(lf)a.skill_code
    #(lf)FROM job_open.job_table2 a
    #(lf)WHERE CONCAT(a.job_no,'-',a.job_rfa_no) IN (" & keylist & ");")
in
    Source1

General Comment
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    I would suggest you create a single SQL query. Actually, you don't need to format the [keylist] in the Power Query. Please try out this one.

     

    SELECT Concat(a.job_no, '-', a.job_rfa_no) AS job_rfa, 
       a.contract_ind, 
       a.skill_code 
FROM   job_open.job_table2 a 
WHERE  Concat(a.job_no, '-', a.job_rfa_no) IN (SELECT 
       Concat(b.job_no, '-', b.job_rfa_no) 
                                               FROM   job_open.job_visit b 
                                               WHERE 
              b.acty_cmpnent_vst_date >= '2018-01-01');

     

    Best Regards,
    Dale

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    I would suggest you create a single SQL query. Actually, you don't need to format the [keylist] in the Power Query. Please try out this one.

     

    SELECT Concat(a.job_no, '-', a.job_rfa_no) AS job_rfa, 
       a.contract_ind, 
       a.skill_code 
FROM   job_open.job_table2 a 
WHERE  Concat(a.job_no, '-', a.job_rfa_no) IN (SELECT 
       Concat(b.job_no, '-', b.job_rfa_no) 
                                               FROM   job_open.job_visit b 
                                               WHERE 
              b.acty_cmpnent_vst_date >= '2018-01-01');

     

    Best Regards,
    Dale