Power BI–Data Source Settings and Excel files

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.

image

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.

image

image

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).

image

  For those of you who are not familiar with that dialog there are three main areas provided:

  1. 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. 2. A preview of the selected query
  3. 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.

image

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

image

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.

image

If you change back to the “Edit Query” dialog you’ll see that there are three steps added to the transformation pipeline.

image

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

Advertisements
This entry was posted in PowerBI and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s