Forum Discussion
Recording PBI table into SQL server
Hi uk-roberto92 , Thank you for reaching out to the Microsoft Community Forum.
Yes, you are on the right track, If you have access to Microsoft Fabric and are comfortable with PySpark and wants maximum scalability. Create a Fabric Lakehouse and a Notebook. In the Notebook, use PySpark to query your Power BI dataset directly with mssparkutils.notebook.pbi_query, passing EVALUATE FinalTable to load the full table into a DataFrame. Export it to SQL Server using the JDBC connector with an overwrite mode to ensure a fresh snapshot. Set up a Fabric Data Pipeline, add the Notebook as a task, and schedule it. This bypasses REST API row limits, making it ideal for large or complex datasets. However, it requires Fabric licensing and PySpark knowledge, so it’s only worth pursuing if your dataset exceeds 100,000 rows or Power Automate’s setup feels limiting.
For the most reliable and immediate solution, I recommend using Power BI Service with Power Automate. Since you’ve confirmed DAX Studio works for a manual export, we’ll build on that. Publish your report to a Power BI Service workspace and schedule a dataset refresh. Create a Power Automate scheduled flow to run shortly after. In the flow, use the “Power BI - Run a Query Against a Dataset” action with your DAX query, EVALUATE FinalTable—to export the full table as JSON. Then, use the “SQL Server - Insert Row” action to parse the JSON and write it to a pre-created SQL table (e.g., PowerBI_Snapshot). For datasets over 100,000 rows, add pagination by modifying the DAX with TOPN and an offset loop in the flow. Publish the workspace as a Power BI App for access. This is fully automated, leverages your existing tools, and requires no extra licensing beyond Power BI Premium (if already in use). It’s efficient for small-to-medium datasets and scalable with minimal effort.
If you have access to Microsoft Fabric and are comfortable with PySpark, I suggest an alternative for maximum scalability. Create a Fabric Lakehouse and a Notebook. In the Notebook, use PySpark to query your Power BI dataset directly with mssparkutils.notebook.pbi_query, passing EVALUATE FinalTable to load the full table into a DataFrame. Export it to SQL Server using the JDBC connector with an overwrite mode to ensure a fresh snapshot. Set up a Fabric Data Pipeline, add the Notebook as a task, and schedule it. This bypasses REST API row limits, making it ideal for large or complex datasets. However, it requires Fabric licensing and PySpark knowledge, so it’s only worth pursuing if your dataset exceeds 100,000 rows or Power Automate’s setup feels limiting.
For a quick manual option that’s already tested, stick with DAX Studio. Connect it to your Power BI Desktop model, run EVALUATE FinalTable, and export the results to a CSV file. Then, use SQL Server Management Studio (SSMS) to import the CSV into your table. This is perfect for one-off snapshots and requires no setup beyond what you’ve already done. However, it’s not automated, so it’s only a stopgap while setting up one of the above solutions.
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,
To record a snapshot of a Power BI table into SQL Server, there are a few ways you could approach it, depending on your specific requirements:
Exporting Data to SQL Server via Power Query or DAX: You could use Power BI's built-in Power Query editor to connect directly to your SQL Server and load the data back into SQL Server using a custom query or even by writing a stored procedure. However, this requires you to transform the data before sending it back to SQL Server, and it may be tricky if the tables in Power BI are complex.
Using Power Automate (Flow): Power Automate allows you to create automated workflows between your Power BI and SQL Server. You could use Power Automate to trigger an action to export the data from Power BI (as a CSV file, for example) and then push that data into your SQL Server database. It allows more flexibility but requires setup in both Power BI and the SQL Server environment.
Export Data via DAX Studio or External Tools: DAX Studio is an excellent tool that allows you to export data directly from Power BI Desktop to different formats like Excel, CSV, or even a database like SQL Server. You can create a connection to the Power BI model and use it to query the table and export it.
Hope this helps!
😁😁
Thanks freginier , I just tried with DAX Studio export and it works fine!
Might be asking too much now, but you know if there's a way to make this automatically? (maybe from PBI App?)
eg update table -> export in SQL
I am now looking into Fabric Notebook with PySpark...feel like I am in the right direction
Hi uk-roberto92 , Thank you for reaching out to the Microsoft Community Forum.
Yes, you are on the right track, If you have access to Microsoft Fabric and are comfortable with PySpark and wants maximum scalability. Create a Fabric Lakehouse and a Notebook. In the Notebook, use PySpark to query your Power BI dataset directly with mssparkutils.notebook.pbi_query, passing EVALUATE FinalTable to load the full table into a DataFrame. Export it to SQL Server using the JDBC connector with an overwrite mode to ensure a fresh snapshot. Set up a Fabric Data Pipeline, add the Notebook as a task, and schedule it. This bypasses REST API row limits, making it ideal for large or complex datasets. However, it requires Fabric licensing and PySpark knowledge, so it’s only worth pursuing if your dataset exceeds 100,000 rows or Power Automate’s setup feels limiting.
For the most reliable and immediate solution, I recommend using Power BI Service with Power Automate. Since you’ve confirmed DAX Studio works for a manual export, we’ll build on that. Publish your report to a Power BI Service workspace and schedule a dataset refresh. Create a Power Automate scheduled flow to run shortly after. In the flow, use the “Power BI - Run a Query Against a Dataset” action with your DAX query, EVALUATE FinalTable—to export the full table as JSON. Then, use the “SQL Server - Insert Row” action to parse the JSON and write it to a pre-created SQL table (e.g., PowerBI_Snapshot). For datasets over 100,000 rows, add pagination by modifying the DAX with TOPN and an offset loop in the flow. Publish the workspace as a Power BI App for access. This is fully automated, leverages your existing tools, and requires no extra licensing beyond Power BI Premium (if already in use). It’s efficient for small-to-medium datasets and scalable with minimal effort.
If you have access to Microsoft Fabric and are comfortable with PySpark, I suggest an alternative for maximum scalability. Create a Fabric Lakehouse and a Notebook. In the Notebook, use PySpark to query your Power BI dataset directly with mssparkutils.notebook.pbi_query, passing EVALUATE FinalTable to load the full table into a DataFrame. Export it to SQL Server using the JDBC connector with an overwrite mode to ensure a fresh snapshot. Set up a Fabric Data Pipeline, add the Notebook as a task, and schedule it. This bypasses REST API row limits, making it ideal for large or complex datasets. However, it requires Fabric licensing and PySpark knowledge, so it’s only worth pursuing if your dataset exceeds 100,000 rows or Power Automate’s setup feels limiting.
For a quick manual option that’s already tested, stick with DAX Studio. Connect it to your Power BI Desktop model, run EVALUATE FinalTable, and export the results to a CSV file. Then, use SQL Server Management Studio (SSMS) to import the CSV into your table. This is perfect for one-off snapshots and requires no setup beyond what you’ve already done. However, it’s not automated, so it’s only a stopgap while setting up one of the above solutions.
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.
