Power BI – Disable Data Refresh for Local Data Source

Requirement: In a Power BI report I have loaded the data using a local CSV file and SQL Server. Data in SQL server will be updated frequently. I have published the report in Power BI server and added data gateway connections to refresh the data in report. But every time refresh runs, data will refresh in datasets of both SQL Server and CSV but there are no updates in CSV file so why it should refresh. I did some research and found a option to disable the refresh for a datasets in Power Query editor. Disabling data refresh will only work in Power BI Desktop, it will not work in Power BI server. So lets have a look at different approaches to do this.

Solution: Data should be entered manually in Power BI Desktop. I have found two approaches to do this

First Approach:

1. Open Power BI Desktop
2. Instead of choosing Get Data, Click on Enter Data
3. Create Table window will open
4. Copy the data from CSV and paste the in the Create table window
5. But here Power BI have the limitation to copy the data of less than 3000 cells



6. So this approach will work only if we have less data I.e around 3000 cells of data

Second Approach:

1. Open Power BI Desktop
2. Load the data from CSV
3. Open Query Editor
4. Remove the step Promoted Headers if the headers are promoted from first row
5. Select all the columns. Right click and click on Merge to merge all the columns
6. Select the comma as separator, click OK. All the columns are merged into one column
7. Convert the column into a List. Convert to List is available in Transform section
8. Click on Advance Editor. New window will open
9. Add the step to combine all rows into one with the separator ‘=’ and the output should be that step



10. Click OK, Copy the text
11. Click on New Source and select Blank Query
12. Click on advance editor for the new query
13. Enter the copied text in source. It will look like below



14. Conver the text into a table

Make data easy with Helical Insight.
Helical Insight is world’s best open source business intelligence tool.

Get your 30 Days Trail Version

15. Click on Split column in Transform
16. Select the Equal sign as separator and choose split into as Rows



17. All rows will be separated. Now we have to split the columns which are separated by comma
18. Click on Split column, choose comma as separator. Click OK
19. Delete the existed datasource which loaded using CSV file
20. Load data from another datasources.
21. Create report and publish
22. Add the schedules to refresh the data
23. We do not need any gateway to refresh the local csv data file as we have embedded the data in Power BI report itself.

Thank You
Ramu Vudugula
BI Developer
Helical IT Solutions Pvt Ltd

Best Open Source Business Intelligence Software Helical Insight Here

A Business Intelligence Framework

Free
Download

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download