Forum Discussion
QuickBooks online Connector pull table date range only
BossBoss it depends if internal QBO connection allows query folding or not, for example, in PQ, you filter for last 24 months, if query folder is possible, it will filter data at source but if query folding is not possible, it will pull all the data and then filter for 24 months, and in that case, I don't see you have any solution.
To check if query folding is happening or not, in Power query add a filter step for last 24 months and then right-click on the filter step and in the menu there is an option called Native Query, if it is greyed out, it means query folding not happening otherwise it does.
I hope it helps.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
So it looks like Native Query is allowed as its not greyed out. However I have never done a filter on an entire table using the Query steps. I have always done it on the front end visual. Can you help me with what I need to adjust? Here is what I have right now using the QB Connector that handles and hides a lot of the connection to QB online.
1. Source Step = = QuickBooksOnline.Tables(null)
2. Navigation = = Source{[Name="Invoice",Kind="Table"]}[Data] (*** This is me wanting to do this on the invoice table ***)
3. Removed Columns = = = Table.RemoveColumns(Custom1,{"Bill_Address_Line4", "Bill_Address_Line5", "Bill_Address_Long"}) (**ME removed Columns I don't need or want****)
4. Custom 1 - I clicked on my "Remove Columns" step and clicked "insert step after" It create a new Custom1 step that says "= Invoice_Table" I assume id do somewthing to this line that says give me Column Transactions in the last 24 months? Can you help with the code please sir.
Or Wait... do you want me to click on the Naviagtion step and choose Native Query? It brings up this super long query. Do I make the change her on the "Last Update_Time" column to only import me the last 24 months of entries? Can you help with the code to do that? Thank you in advance
select "Id",
"SyncToken",
"Create_Time",
"Last_Updated_Time",
"Document_Number",
"Transaction_Date",
"DepartmentRef_Value",
"DepartmentRef_Name",
"CurrencyRef_Value",
"CurrencyRef_Name",
"Exchange_Rate",
"Private_Note",
"Transaction_Status",
"TxnTaxDetail_TxnTaxCodeRef_Value",
"TxnTaxDetail_TxnTaxCodeRef_Name",
"TxnTaxDetail_Total_Tax",
"CustomerRef_Value",
"CustomerRef_Name",
"CustomerMemo_Value",
"Bill_Address_Id",
"Bill_Address_Line1",
"Bill_Address_Line2",
"Bill_Address_Line3",
"Bill_Address_Line4",
"Bill_Address_Line5",
"Bill_Address_City",
"Bill_Address_Country",
"Bill_Address_Country_SubDivision_Code",
"Bill_Address_Postal_Code",
"Bill_Address_Note",
"Bill_Address_Lat",
"Bill_Address_Long",
"Ship_Address_Id",
"Ship_Address_Line1",
"Ship_Address_Line2",
"Ship_Address_Line3",
"Ship_Address_Line4",
"Ship_Address_Line5",
"Ship_Address_City",
"Ship_Address_Country",
"Ship_Address_Country_SubDivision_Code",
"Ship_Address_Postal_Code",
"Ship_Address_Note",
"Ship_Address_Lat",
"Ship_Address_Long",
"ClassRef_Value",
"ClassRef_Name",
"SalesTermRef_Value",
"SalesTermRef_Name",
"Due_Date",
"Global_Tax_Calculations",
"ShipMethodRef_Value",
"ShipMethodRef_Name",
"Ship_Date",
"Tracking_Number",
"Total_Amount",
"Home_Total_Amount",
"Apply_Tax_After_Discount",
"Print_Status",
"Email_Status",
"Bill_Email_Address",
"DeliveryInfo_DeliveryType",
"DeliveryInfo_DeliveryTime",
"Balance",
"Home_Balance",
"Transaction_Source",
"DepositToAccountRef_Value",
"DepositToAccountRef_Name",
"Deposit",
"Transaction_Location_Type"
from "QuickBooks"."QuickBooks"."Invoice"