QuickBooks online Connector pull table date range only

[BossBoss]

I am using the Desktop version of PBI with the QuickBooks (QBO) connector. It get Authenticated just fine and chose the tables I want to import with no issues. I have built some great reports with wonderful visuals.

However, now I have an issue because I am pulling down the entire Invoice and Payments table and that takes about 10-12 minutes every time I refresh as I have 17k entries.  Where in the query that pulls the table via the connector can I tell it I only want the data with dates in the last 24 months.  

I know how to manipulate data once it gets into PBI. This question is about only having the QBO connector only give me 24 months of table data and not the entire Table contents.

[v-xicai]

Hi @BossBoss ,

There is no documents about the 24 month date range limitation for the QuickBooks online Connector currently. You may try parry2k's suggestion to check if the query folder is possible. And if there is still no approach to achieve your requirement,  you may post your new idea in Idea Forum , add your comments there to improve Power BI and make this feature coming sooner.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@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.⚡

[BossBoss]

@parry2k 

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"