Problem Description 1:

How to connect to QuickBooks Data with Power BI using QODBC Desktop?

Solutions: 

Open the QODBC setup screen and change the QODBC Compatibility mode from "Default" to "3.8."

To change, please follow the steps below:

Start >> All Programs >> QODBC Driver for QuickBooks >> Configure QODBC Data Source >>Go To "System DSN" Tab>> select DSN "QuickBooks Data" >> click "Configure”>> Switch to "Advanced" tab>> Navigate to "QODBC Compatibility">> change to "3.8"

For QODBC POS, select DSN "QuickBooks POS Data."

For QODBC Online, select DSN "QuickBooks Online Data."

Start QuickBooks and log in to the QuickBooks company file as the QuickBooks user Admin.

Start Microsoft PowerBI Desktop (32-bit).

Navigate to the "Get Data" option, and from the drop-down list, select "More."

A pop-up with the title "Get Data" should appear.

Select "Other" --> "ODBC" and click the "Connect" button.

Select the "QuickBooks Data" from the Data source name (DSN) list and click the "OK" button.

Note: If you are using QODBC version 19.0.0.333 or lower, use the "Advanced options">> Write the SQL statement for the table you want to fetch data from.

For example, "select * from customer" and click the "OK" button.

It would be best if you got the preview of the table. Click the "Load" button to continue.

Select the fields you want to display on the report. This will add the fields to the information and display the data.

If you are facing "Driver does not support this parameter," please switch QODBC, QRemote 32-Bit DSN, and QRemote 64-Bit DSN to ODBC Compatibility 3.8.

To change compatibility in QODBC, follow the path below:
Start >> All Programs >> QODBC Driver for QuickBooks >> Configure QODBC Data Source >>Go to "System DSN" Tab>> select DSN "QuickBooks Data" >> click "Configure”>> Switch to "Advanced" tab>> Navigate to "QODBC Compatibility">> change to "3.8"

For QODBC POS, select DSN "QuickBooks POS Data."

For QODBC Online, select DSN "QuickBooks Online Data."

If you are using Power BI 64-bit, you need to change to QODBC and QRemote 64-bit clients. To change compatibility in QODBC, follow the path below:
Start >> All Programs >> QODBC Driver for QuickBooks >> Configure QODBC Data Source >>Go to "System DSN" Tab>> select DSN "QuickBooks Data" >> click "Configure”>> Switch to "Advanced" tab>> Navigate to "QODBC Compatibility">> change to "3.8"

For QODBC POS, select DSN "QuickBooks POS Data."

For QODBC Online, select DSN "QuickBooks Online Data."

To change compatibility in QRemote 32-bit, follow the path below:
Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source >>Go to "System DSN" Tab>> select "QuickBooks Data QRemote" DSN >> click "Configure">> Switch to Advanced tab and change 'ODBC Compatibility' to '3.8' and click the Apply/OK button.

For QODBC POS, select DSN "QuickBooks POS Data QRemote."

For QODBC Online, select DSN "QuickBooks Online Data QRemote."

To change compatibility in QRemote 64-bit, follow the path below:
Start >> All Programs >> QODBC Driver for use with QuickBooks (64-Bit)>> Configure QODBC Data Source >>Go to "System DSN" Tab>> select "QuickBooks Data 64-bit QRemote" DSN >> click "Configure">> Switch to Advanced tab and change 'ODBC Compatibility' to '3.8' and click the Apply/OK button.

For QODBC POS, select DSN "QuickBooks POS Data 64-Bit QRemote."

For QODBC Online, select DSN "QuickBooks Online 64-Bit Data QRemote."

Try connecting again. It should fix the issue you are facing.
If you are using the older version of Power BI, please download the latest and try connecting with QODBC.

Problem Description:

We would wish to inactive items that have not sold after a certain date. Is this possible? If so, any help would be appreciated.

Solution:

1) Get the list of Sold Items by exporting the SalesByItemSummary report for a certain period in MS Excel as below

For Example:
sp_report SalesByItemSummary show Text, Label, Quantity, Amount, Percent, AveragePrice parameters DateFrom = {d '2016-06-01'}, DateTo = {d '2016-12-31'}

Please refer to How to create sp_reports using Microsoft Excel for exporting reports into Excel.

By running the above query, you will get ItemType + Parent Item Name in the Text column & ItemName + description in the Label column.

We will split ItemName from the description using the "Text to Columns" option. We will select the "Label" column & click on "Text to Columns."

Choose "Delimited" and click "Next."

Select "Other" and write "(" (i.e., Opening Brace) and click "Next." We are splitting data into different columns from Opening Brace.

You can see split data from the Data preview. Now click "finish."

Click "OK" to replace destination cell contents.

Data is split into two columns.

Now we will add a new column & use the formula for trimming whitespace from the Child Item name & removing the prefix "Total" from the Parent Item name.

=TRIM(SUBSTITUTE(C2,"Total","",1))

The formula is applied to all rows. Now we have furnished the Item Name.

2) Now we have the Item Name & Item type for sold items. Now we need to export Item tables depending on Item Type (i.e., ItemInventory, ItemNonInventory, ItemService, etc.).

In this example, we will export the ItemInventory table in sheet2 & we will make inactive ItemInventory that is unsold for a certain period.

Please refer to Using QuickBooks Data with Excel for exporting the ItemInventory table into Excel.

The ItemInventory table is exported to sheet2.

Now we will go to sheet 3 & use the VLOOKUP formula for getting a list of unsold Items.
=IF(IFERROR(VLOOKUP(TRIM(Sheet2!E1), Sheet1!B: B,1, FALSE), "ERROR")=" ERROR," Sheet2!E1,"")

The formula is applied to all rows. Now we have the name of the unsold ItemInventory.

3) Now, we have the name of the unsold ItemInventory. You can make it inactive by running the following update query.

Update ItemInventory set IsActive = False where Name = 'Cabinet Pulls'

You can also use VBA to make all unsold Items inactive. Please refer to Using QuickBooks Data with VBA and write a VBA script per your requirement.

Result in QuickBooks.

You can repeat the above steps for Item Type like ItemNonInventory, ItemService, etc...