In my previous post I explained the new possibility of changing data source settings like database server and or database with just a few mouse clicks. This concept works well for SQL Server connections, but with Excel files the way to change the import connection afterwards is a little bit different.
Let’s start with a new Power BI Desktop file and import data from a local Excel file.
Data Source Settings with Excel Files
With the new way of editing “Data Source Settings” the dialog does not provide the action of changing the path to our import Excel file. Only administration of permissions is possible.
Not ideal, but there is a way of changing the path to the import Excel File afterwards.
Changing the Path to Excel File
The way to success is hidden behind in the “Edit Queries” action (just above the Data Source Settings menu).
For those of you who are not familiar with that dialog there are three main areas provided:
- 1. A list of the queries in the current Power BI file. In my case I’ve only imported one Excel table it’s just Table1 here.
- 2. A preview of the selected query
- 3. in the Query Settings / Applied Steps area you’ll find all the applied data querying or transformation steps.
If you hover over the steps (in “Applied Steps” section), you can either delete that step or edit the settings of that specific step. And that is the solution for changing our import path.
If you’ll open the settings gear for the Source step the change of the path is easy. Just select the new file (recommended with a similar structure than the original file) and
Changes in Source file structure
If the new Excel file contains more columns than the original one – no problem! New columns are added to the data set and those entries can be analyzed.
The other way round – less columns than the original structure – no problem! the missing columns are removed from the data set.
If the structure of the new file is not compatible with the old structure you are able to close the Edit Query dialog but afterwards you’ll get some error messages indicating that Power BI Desktop is not able to find the “old” columns.
If you change back to the “Edit Query” dialog you’ll see that there are three steps added to the transformation pipeline.
Step “Source” is responsible for selecting the source file; in the preview table two objects (Sheet1 / Table1) from the Excel file are displayed.
The next step “Navigation” selects the data object “Table1” (remember my selection during the initial Excel import).
The third step “Changed Type” is the source of error –> Power BI changed the type of the Tag column (which means day in English). I’ll delete this step and “close& apply” the editing actions: E voila! The dataset is updated with the new structure