Log In
Register
Home
Power Query
October 7, 2022
Offline
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
Trusted Members
October 18, 2018
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
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.
Power Query
October 7, 2022
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
The answer should be here:
Power Query
October 7, 2022
Offline
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
1 Guest(s)
