Forum Discussion
Powerbi Netsuite Automation
Hi KK4321 , Thank you for reaching out to the Microsoft Community Forum.
This typically indicates file access issues, network delays, or gateway configuration problems. The most likely root cause is the PowerShell script locking the Excel file while Power BI attempts to refresh, causing the gateway to fail to open a timely connection. Additionally, using Excel files with frequent updates and large transformations adds fragility to the system.
In the short term, you should refactor your PowerShell script to write to a temporary file first, then overwrite the original only after the refresh is complete, this avoids locking issues. Use UNC paths, ensure proper gateway permissions and stagger PowerShell and Power BI refreshes to avoid overlaps. Consider switching to CSV instead of Excel, as it reduces locking and improves performance. For longer-term, either move to NetSuite’s SuiteAnalytics ODBC connection (if available) or adopt a cloud-based architecture using Microsoft Fabric and OneLake, which allows automated data ingestion via APIs or pipelines and eliminates on-premises gateway dependency.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.Hi KK4321 , Thank you for reaching out to the Microsoft Community Forum.
No, the NetSuite API doesn’t connect directly to Fabric or OneLake, instead, you use something in the middle like a Python script, Power Automate, or another tool to pull the data from NetSuite. That tool is what connects to the API, gets the data and then saves it into OneLake.
Think of it like this:NetSuite API -> your script/tool -> OneLake -> Power BI (via Fabric)
Once the data is in OneLake, Fabric can pick it up using a Dataflow, Notebook, or Pipeline, and from there, Power BI can connect and refresh automatically.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hi KK4321 , Thank you for reaching out to the Microsoft Community Forum.
This typically indicates file access issues, network delays, or gateway configuration problems. The most likely root cause is the PowerShell script locking the Excel file while Power BI attempts to refresh, causing the gateway to fail to open a timely connection. Additionally, using Excel files with frequent updates and large transformations adds fragility to the system.
In the short term, you should refactor your PowerShell script to write to a temporary file first, then overwrite the original only after the refresh is complete, this avoids locking issues. Use UNC paths, ensure proper gateway permissions and stagger PowerShell and Power BI refreshes to avoid overlaps. Consider switching to CSV instead of Excel, as it reduces locking and improves performance. For longer-term, either move to NetSuite’s SuiteAnalytics ODBC connection (if available) or adopt a cloud-based architecture using Microsoft Fabric and OneLake, which allows automated data ingestion via APIs or pipelines and eliminates on-premises gateway dependency.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
v-hashadapu thanks for your prompt response. I will try the second solution using Fabric and one data lake as Netsuite analytics ODBC didnt work. Does this mean API will connect to either Fabric or One lake? Have never used this before.
Hi KK4321 , Thank you for reaching out to the Microsoft Community Forum.
No, the NetSuite API doesn’t connect directly to Fabric or OneLake, instead, you use something in the middle like a Python script, Power Automate, or another tool to pull the data from NetSuite. That tool is what connects to the API, gets the data and then saves it into OneLake.
Think of it like this:NetSuite API -> your script/tool -> OneLake -> Power BI (via Fabric)
Once the data is in OneLake, Fabric can pick it up using a Dataflow, Notebook, or Pipeline, and from there, Power BI can connect and refresh automatically.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
