Forum Discussion
Filter SQL Query upon Import
Hello all-
I have created a custom SQL pull and am importing the data with the SQL Import function. My data source is so large that importing this is taking a long time. I am bringing in All customer information, but then having to use a custom dynamic customer list to filter to a small subset. Is there anyway to automate this on import to reference the other Customer filter upon import?
Example below:
SQL IMPORT: (All Data)
Customer # // Product // Name
1 // 123 // Joe
2 // 123 // Sam
3 // 123 // Hailie
4 // 123 // Jim
5 // 124 // John
Customers Enrolled Today (filter list)
Customer #
2
4
6
*Net I would only want to pull in Customers Enrolled in the initial SQL data import.
Any help would be greatly appreciated!
Thank you
Hi Anonymous
your query should be something like this:
= Sql.Database("XXXXX", [Query= "SELECT DIS.Location_cd , DPR.ProductGroup_nm , DPR.ProductGroup_cd , DPR.ProductSequence_nm , DPR.ProductSequence_cd , DDT.Fiscal_year_nr , DDT.Fiscal_month_year_nr , DDT.Fiscal_week_year_nr --METRICS , sum(FID.Units) AS Units , sum(FID.Sales) AS Sales , sum(FID.GL_Gross_Profit_FIFO) AS GL_Gross_Profit_FIFO , sum(FID.Standard_Gross_Profit) AS Standard_Gross_Profit -- select count(1) FROM DW.vwFact_InvoiceDetail(NOLOCK) FID INNER JOIN DW.vwDim_Currency DCR ON DCR.CurrencyKey = FID.CurrencyKey INNER JOIN DW.vwDim_InvoiceDetail DID ON DID.InvoiceDate_id = FID.InvoiceDate_id AND DID.InvoiceDetail_id = FID.InvoiceDetail_id INNER JOIN DW.vwDim_Date DDT ON DDT.Date_id = FID.InvoiceDate_id INNER JOIN DW.vwDim_Organization DOR ON DOR.Organization_id = FID.Organization_id INNER JOIN DW.vwDim_Product DPR ON DPR.Product_id = FID.Product_id INNER JOIN DW.vwDim_InvoiceHeader DIH ON DIH.Invoice_id = FID.Invoice_id INNER JOIN DW.vwDim_Customer_OLAP DIB ON DIB.Customer_id = FID.BillCustomer_id AND DIB.Customer_Class_cd != '28' INNER JOIN DW.vwDim_Customer_OLAP DIS ON DIS.Customer_id = FID.ShipCustomer_id WHERE DID.Revenue_GL_no IN ( '40001' ,'40002' ,'40003' ,'40004' ,'40005' ,'40006' ,'40007' ,'40008' ,'40101' ,'40102' ,'40103' ,'40104' ,'40105' ,'40107' ,'40108' ,'50001' ,'50002' ,'50003' ,'50005' ,'50101' ,'50102' ,'50103' ,'50105' ) AND DID.Record_cd IN ('B','C','D','G','N') AND DIH.Preacquisition_flg ='N' AND DOR.Reporting_Division_cd IN ('D01','D99') AND DIS.Customer_Channel_nm ='CORE' AND DDT.Fiscal_year_nr IN (2019) -- AND ddt.Fiscal_week_year_nr = 201712 AND DIS.Location_cd IN (" & TheCommaDelimitedValuesShownInMyFirstReply & ") GROUP BY DIS.Location_cd , DPR.ProductGroup_nm , DPR.ProductGroup_cd , DPR.ProductSequence_nm , DPR.ProductSequence_cd , DDT.Fiscal_year_nr , DDT.Fiscal_month_year_nr , DDT.Fiscal_week_year_nr"])
8 Replies
- LivioLanzo
Solution Sage
Hello Anonymous -
you can create a concatenated string of the customers isd of the Filter table and then pass it to the SQL for the Database query
source = Oracle.Database("_SERVER_NAME",
[Query="Select Customer, Product, Name
From Customers
WHERE Customer IN (" & CustomersList &")"])
Sorry I am pretty new at PowerBI in general. How would I create a concatenated string with it in rows?
- LivioLanzo
Anonymous
You select the Customer column from the filter table and then fo to the Transform tab within Power Query and choose 'Convert to list'. Then You wrap the resulting formula with Text.Combine.
If the Customer ID on your side is text and not an integer then you need to add single quote as a delimiter
if a number then you do = Text.Combine(#"Changed Type"[Customer], ",")
if text then you do = "'" & Text.Combine(#"Changed Type"[Customer], "','") & "'"