Forum Discussion
Filter SQL Query upon Import
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"])
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?
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], "','") & "'"
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_nrAnonymous
what is the Power Query code which import the sql table?