ExtendInsights does an amazing job of getting your data into Excel, eliminating the need to copy/paste CSV exports. Users can build out dashboards and other models in Excel, and, with the push of a button, or even on a hands-off schedule, ExtendInsights will deliver the most recent results from the saved search to Excel.
To take this further, Extend recommends using Excel Power Query to analyze and model your data safely and at scale.
This guide explains why Power Query is recommended and how to use it with ExtendInsights Analytics.
Why Use Power Query with ExtendInsights?
We recommend using ExtendInsights to deliver your data to an Excel workbook, which will then become your source data, accessed via Power Query, either from Excel or Power BI.
Key Benefits
Using Power Query helps you:
Power Query will protect the integrity of your master data sheet
Power Query will not overwrite formulas in your Excel file
If you're using ExtendInsights to run your saved searches on a schedule it will minimize issues with other users editing the worksheet while it's being updated
Power Query can be used to transform your data if desired
Power Query can be used to relate unlimited queries together by common key fields
Power Query can consolidate multiple files in a single SharePoint folder into one data model
Power Query allows users to go well beyond the 1 million row limit of Excel
Power Query is ridiculously fast
💡 Good to know
Power Query is recommended, but not required.
If you’re building simple models for personal use and not adding formulas to ExtendInsights-managed tables, you may choose to work entirely within one Excel workbook.
Build Your First Model with ExtendInsights + Power Query
Walkthrough Video
Watch the accompanying video to see it in action.
Step 1: Create the Source Data Workbook
This workbook is where ExtendInsights writes NetSuite data.
Create a new Excel file
Save it to OneDrive for Business or SharePoint
We recommend including “Source Data” in the file name
Open the ExtendInsights Analytics add-in
Add your NetSuite saved search
(Optional but recommended) Put the search on a schedule
📘 New to ExtendInsights? Refer to the Getting Started guide.
Copy the File Path
You’ll need the file path in the next step.
In Excel, go to File > Info
Select Copy Path
You can now close the source workbook.
That's it!
You have now successfully created your source data file. If you've put this on a schedule (recommended), you won't need to touch it again. If you have not, then you will still need to do a manual refresh with ExtendInsights (but hey, it sure beats copy/paste from CSV files).
Step 2: Create the Analysis Workbook
This is the fun part. We're going to show you how to connect to your source data. You'll be amazed at all Power Query can do.
This workbook is where you build dashboards, formulas, and models
Create a new Excel file
Save it to OneDrive for Business or SharePoint
Go to Data > Get Data > From Other Sources > From Web
⚠️ Do not choose “From File”
Connect to the Source File
Next, paste the data you copied from File > Info > Get Path from the previous section.
For some weird reason, you also need to remove the
?web=1from the URL (let's just call it a Microsoft quirk).Click OK
Select the Data
If prompted, sign in with your Microsoft 365 account.
➡️ First time signing in, follow the steps here on How to Log in
Choose the table name, not the worksheet name
This automatically promotes headers
Click Load
ℹ️ Once logged in, the list of names will show. Choose the table name, not the worksheet name. Doing so will automatically promote your headers and save you a step later.
💡 Note
Table names are derived from:
ExtendInsights query names, or
NetSuite saved search names
Your data is now loaded via Power Query.
✅ You’re Ready to Analyze
Your analysis workbook now:
Pulls the latest data from the source file
Updates without overwriting formulas
Supports advanced modeling and dashboards
🔐 Logging In to Microsoft 365 (If Prompted)
If it's your first time logging in, you will be prompted to enter your Microsoft credentials.
Select Organizational account
Enter your Microsoft credentials
Click Connect
This is expected behavior the first time you connect.
Bonus Tips & Best Practices
🔵 Put your data queries on a schedule
Fully automate your dashboard and reporting from end to end by scheduling your queries or scheduling your NetSuite saved searches
Schedule Power Query refreshes:
Go to Data > Queries & Connections
Right-click the query > Properties
Set refresh frequency
ℹ️ Manual Refresh
Click Data >Refresh in the ribbon to manually refresh your data from the source file.
🔵 Add persistent formulas to your table
Do not add formulas to ExtendInsights-managed tables
Add formulas in the analysis workbook instead
Power Query refreshes will not overwrite them
🔵 Cleanse your source file with Power Query
Not every source file has perfect data. Excel Power Query can help clean up your data every time it runs.
Use Power Query to:
Remove unwanted columns
Fix data types
Standardize values
These transformations run automatically on each refresh.
🔵 Connect multiple queries Together
Power Query allows you to:
Combine unlimited datasets
Relate them by common key fields
Build robust data models
We've just uncovered the beginning when it comes to Power Query. Here's a link to another article we wrote that shows you how to add your queries to a data model and relate them by common key fields. Now you can relate an unlimited number of queries together.
📘 See the related article on building data models with Power Query.
Need Help?
Before contacting Support, try searching:
If you still need assistance:
💬 Use in-app chat from within ExtendInsights
✉️ Email cloudextend-support@celigo.com
📎 Include screenshots and details about your workbook setup when possible.