cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 # 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Filter SQL Query upon Import

Hi @cgoldstein 

 

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"])

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

8 REPLIES 8
Highlighted
Solution Sage
Solution Sage

Re: Filter SQL Query upon Import

Hello @cgoldstein  - 

 

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 &")"])

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Frequent Visitor

Re: Filter SQL Query upon Import

@LivioLanzo 

Sorry I am pretty new at PowerBI in general. How would I create a concatenated string with it in rows? 

Highlighted
Solution Sage
Solution Sage

Re: Filter SQL Query upon Import

@cgoldstein 

 

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], "','") & "'"

 

Capture.PNGCapture2.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Frequent Visitor

Re: Filter SQL Query upon Import

@LivioLanzo 

 

Thank you for your extra detail! I've got it loaded to a wrapped list like your instructions have it.

 

Is the syntax to pass it to the import portion in DAX or SQL? Currently, I'm connected with a SQL Server Database with the SQL Statement loading it in. I tried to add the statement but wasn't getting it to work for me.

 

SQL Script Below-

My Query Name for the filter = TP_Filter_SQL

My List Column = = Text.Combine( #"Removed Columns"[LocationNumber], ",")

 

 

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)
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

 

Highlighted
Solution Sage
Solution Sage

Re: Filter SQL Query upon Import

@cgoldstein 

 

what is the Power Query code which import the sql table?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Frequent Visitor

Re: Filter SQL Query upon Import

@LivioLanzo 

 

I started looking and found the translation from SQL into DAX. When you go to the Query > click into Source on steps and shows up in the top row.

 

In my import, "Location_cd" is "Customer #" I want to filter on.

 

See below:

 

= Sql.Database("12345, "XXYYZZ", [Query="SELECT DIS.Location_cd#(lf), DPR.ProductGroup_nm#(lf), DPR.ProductGroup_cd#(lf), DPR.ProductSequence_nm#(lf), DPR.ProductSequence_cd#(lf), DDT.Fiscal_year_nr#(lf), DDT.Fiscal_month_year_nr#(lf), DDT.Fiscal_week_year_nr#(lf)--METRICS#(lf), sum(FID.Units) AS Units#(lf), sum(FID.Sales) AS Sales#(lf), sum(FID.GL_Gross_Profit_FIFO) AS GL_Gross_Profit_FIFO#(lf), sum(FID.Standard_Gross_Profit) AS Standard_Gross_Profit -- select count(1) #(lf)FROM DW.vwFact_InvoiceDetail(NOLOCK) FID #(lf)INNER JOIN DW.vwDim_Currency DCR ON DCR.CurrencyKey = FID.CurrencyKey#(lf)INNER JOIN DW.vwDim_InvoiceDetail DID ON DID.InvoiceDate_id = FID.InvoiceDate_id AND DID.InvoiceDetail_id = FID.InvoiceDetail_id#(lf)INNER JOIN DW.vwDim_Date DDT ON DDT.Date_id = FID.InvoiceDate_id#(lf)INNER JOIN DW.vwDim_Organization DOR ON DOR.Organization_id = FID.Organization_id#(lf)INNER JOIN DW.vwDim_Product DPR ON DPR.Product_id = FID.Product_id#(lf)INNER JOIN DW.vwDim_InvoiceHeader DIH ON DIH.Invoice_id = FID.Invoice_id#(lf)INNER JOIN DW.vwDim_Customer_OLAP DIB ON DIB.Customer_id = FID.BillCustomer_id AND DIB.Customer_Class_cd != '28'#(lf)INNER JOIN DW.vwDim_Customer_OLAP DIS ON DIS.Customer_id = FID.ShipCustomer_id#(lf)WHERE DID.Revenue_GL_no IN (#(lf) '40001' ,'40002' ,'40003' ,'40004' ,'40005' ,'40006'#(lf) ,'40007' ,'40008' ,'40101' ,'40102' ,'40103' ,'40104'#(lf) ,'40105' ,'40107' ,'40108' ,'50001' ,'50002' ,'50003'#(lf) ,'50005' ,'50101' ,'50102' ,'50103' ,'50105'#(lf) )#(lf)AND DID.Record_cd IN ('B','C','D','G','N') #(lf)AND DIH.Preacquisition_flg ='N'#(lf)AND DOR.Reporting_Division_cd IN ('D01','D99')#(lf)AND DIS.Customer_Channel_nm ='CORE'#(lf)AND DDT.Fiscal_year_nr IN (2019)#(lf)-- AND ddt.Fiscal_week_year_nr = 201712#(lf)GROUP BY DIS.Location_cd#(lf), DPR.ProductGroup_nm#(lf), DPR.ProductGroup_cd#(lf), DPR.ProductSequence_nm#(lf), DPR.ProductSequence_cd#(lf), DDT.Fiscal_year_nr#(lf), DDT.Fiscal_month_year_nr#(lf), DDT.Fiscal_week_year_nr", CreateNavigationProperties=false])

 

 

Highlighted
Solution Sage
Solution Sage

Re: Filter SQL Query upon Import

 

Hi @cgoldstein 

 

the 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"])

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Solution Sage
Solution Sage

Re: Filter SQL Query upon Import

Hi @cgoldstein 

 

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"])

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors