Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @mjohnsonuk,
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
Hi @mjohnsonuk,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |