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 pubished the report in Power BI server and added data gateway connections to refresh the data in report. But everytime 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 dataset 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

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.