How do I use an Insights Data Source in Excel?

In today’s data-driven world, the ability to connect Excel to external data sources can significantly improve productivity and data accuracy. By linking Excel to your Insights Data Source, you can automate data imports, ensure your spreadsheets always display the latest information, and streamline your analysis workflow. This guide provides clear, step-by-step instructions for connecting Excel to a data source URL and loading the data into your workbook.

Step 1: Identify Your Data Source Link

1. Log in to your Qtrac account.
2. Go to the Insights tab and locate the report you'd like to use.
   1. Note: At this point you should have all relevant fields selected in your Insights report. Changing the fields after modifying the data (Step 3) can result in errors when refreshing the query if the field names/order do not match.
3. Hover over the 3 dots (⋯) on the right-hand side and select Data Source.
   
4. Click Copy to copy your Data Source URL for use in Step 3.
   

Step 2: Open Excel and Prepare Your Workbook

1. Launch Excel and open a new or existing workbook where you want to import the data.
2. Identify the worksheet where the imported data should appear.

Step 3: Connect to Your Data Source

1. Go to the Data tab in the Ribbon.
2. In the Get & Transform Data section, choose From Web.
   
3. In the dialog box, enter the data source URL from Insights in Step 1.
4. Click OK to establish the connection and open the Power Query editor.
   
5. In the Power Query editor, go to the Home tab and select Data source settings.
   
6. With your query selected, click on Edit Permissions.
   
7. Under Credentials, click Edit.
   
8. Select Basic from the available options and enter your Qtrac user login credentials. Click Save.
   
9. Click OK, and lastly Close.
   
10. In the Home tab, click Refresh Preview. You should now see a list of records appear in the editor.
    

Step 4: Select and Load the Data

1. With your initial data loaded, select the List Tools - Transform tab and select To Table.
   
2. In the dialog box, leave the defaults and select OK.
   
3. Click on the Expand button next to the column name. Uncheck "Use original column name as prefix" and click OK. This will expand your data into a full table.
   
4. Filter, sort, and modify the data to fit your needs.
5. Once the data has been formatted to your specifications, select either the Close & Load or Close & Load To... options in the Home tab to load your query to your workbook.
   1. Close & Load will load the table onto a new worksheet in Excel.
   2. Close & Load To... will allow you to choose an existing worksheet to load the table, as well as a few other load options such as Pivot Tables, Pivot Charts, or Connection Only.
      

Step 5: Refreshing Your Data

If your Insights report references a dynamic timeframe you will need to occasionally refresh your data to see the most up-to-date information. Below are a few ways to accomplish this.

• Option 1 (Recommended): Right-click the table where your data is being loaded and click Refresh.
  
  
  
• Options 2: Click into the table where your data is being loaded, go to the Data tab, and click Refresh or Refresh All depending on your needs.
  
  
  
• Option 3: Go to the Data tab and select Queries & Connections to open a side panel. Locate your query, right-click, and select Refresh
  
  

Please note that depending on the size of your report, the refresh times can vary. Please allow for the data source to fully load before refreshing the query again.