Hi,
I use an ODBC function to extract a transaction report out of Quickbooks Desktop. I would like to dynamically filter the name of the customer in the ODBC script. For that I have created a parameter query from a table with one line item that has a drop-down for the customer name. I tried to reference the parameter in the code below, but it returns zero records in the table. My parameter query is called JobName. I wonder if it is just a syntax issue in how I try to reference the parameter. I appreciate any help:
let
Source = Odbc.Query("dsn=QuickBooks Data 64-Bit QRemote", "sp_report ProfitAndLossDetail show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, ClearedStatus_Title, SplitAccount_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Amount, RunningBalance parameters DateFrom={d'2022-01-01'}, DateTo={d'2023-04-30'}, AccountFilterType = 'CostOfSales', EntityFilterFullNameWithChildren = JobName, ReportBasis = 'Cash'"),
in
#"Changed Type1"
Thanks,
Dana
What does the Mcode look like for the Parameter named JobName?
It should look like this:
let
Source = Excel.CurrentWorkbook(){[Name="TableX"]}[Content],
JobName = Source{0}[JobName]
in
JobName
Where TableX should be the parameter table
Salut Dana,
this:
"sp_report ProfitAndLossDetail show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, ClearedStatus_Title, SplitAccount_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Amount, RunningBalance parameters DateFrom={d'2022-01-01'}, DateTo={d'2023-04-30'}, AccountFilterType = 'CostOfSales', EntityFilterFullNameWithChildren = JobName, ReportBasis = 'Cash'"
should look like:
"sp_report ProfitAndLossDetail show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, ClearedStatus_Title, SplitAccount_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Amount, RunningBalance parameters DateFrom={d'2022-01-01'}, DateTo={d'2023-04-30'}, AccountFilterType = 'CostOfSales', EntityFilterFullNameWithChildren = " & JobName & ", ReportBasis = 'Cash'"
I marked in red the double quotes so you can see where they start/end.
Hi,
Thank you for the suggestions. Catalin's syntax suggestion seems to work, but i am getting the error message:
Formula.Firewall: Query 'COGS by job' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Not sure what to do from here.
Multumesc!
Dana
The answer should be here:
https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/
Thank you! That helps. I was trying to filter the data that is brought in directly in the source step, to help with loading time. But seems like that is not possible so I have to be ok with that 🙂
Really appreciate the extra help.
Dana
