Power BI is continually expanding its list of accessible data sources. Power BI Developers may have noticed “Power BI Datasets” as a new data sourcing option in Power BI Desktop. Power BI Datasets allow users to build reports off of pre-built Power BI models. Unfortunately for developers, changing your data source to a Power BI Dataset for existing reports may not always be straight forward. Below is the process for changing your data source in Power BI desktop to a Power BI Dataset.
- Open the Power BI Desktop file you want to change the data source in.
- Select the “Transform Data” button under the “Home” tab, and select “Transform data” in the drop-down list.
- In Power Query, hold down your “Shift” key to select all of the queries on the left-hand side. Right-click on your selected queries and select the delete option. Make sure none of your initial queries remain and select the “Close & Apply” button in the top left corner.
- Your report visuals will produce an error. To repopulate our visuals with the same data, click the “Power BI datasets” option under the “Home” ribbon.
- You will then be required to select a “Power BI Dataset”. When updating the data source, you should select the dataset that contains the same table names and field names of the report. When you connect to one of these datasets, the connection type will be a “live connection”. A live connection does not require a refresh on the report itself and the report data will be as current as the dataset you connect to.
- After selecting your dataset and clicking “Create”, most of your report visuals should repopulate. Make sure all visuals have repopulated on each page.
- Some visuals using measures or custom columns may not populate immediately but can be fixed with small updates to the backend DAX or by making small formatting changes. If the visuals do not populate at all, investigate the dataset that you are connected to in order to confirm that the table names and field names match. Notice the connection type in the bottom right corner.
While there currently isn’t a quick way to convert data sources in existing reports to an established Power BI dataset, these steps should help with the manual process of moving to an easier way to manage the data in Power BI by using datasets.