Power Bi Get Data From Multiple Excel Files

I have explained in another video previously about how to set up your Power BI to get data from a file in OneDrive for Business folder without needing gatewa. STEP 2: Open a NEW Excel Workbook and go to Power Query From File From Folder. STEP 3: From the Folder dialogue box, click the Browse button. This will bring up the Browse for Folder dialogue box and you need to select the folder you created in Step 1 and press OK. This is how you can use Power Query load multiple files from folder feature. For other file types, like XML Table (.xml) or text (.txt) files, you can use Get & Transform to query, transform, and load that data into an Excel or Power BI Desktop file first. You can then import the Excel or Power BI Desktop file into Power BI. Where you store your files makes a significant difference, too.

This post was contributed by Ken Puls and Miguel Escobar, Power BI experts and authors of the new ebook, '[M]agic Tricks for Data Wizards'.

Have you ever wanted to consolidate, combine or append data from multiple excel spreadsheets or workbooks? I bet you have, and it has been a painful process either done manually or via 3rd party add-ins. In this blog post, we’ll show you how, with the newest version of the Power BI Desktop and Power Query for Excel, you can combine multiple data from Excel files into one big tall table.

The Scenario

In this specific case, we have an Office 365 group that we’ve created where we store some external sales data that do not come from our system. These files are provided by a 3rd party in order to give us a better understanding of the whole market and how well some products are doing on each market. Here’s a few remarks about our case:

Each of these files has only 1 month of datawe’ll have a file for January with just 1 sheet in it that will have all of the data for January.

These are all Excel files living a happy life in SharePoint – we get these files via email from the provider and then store them on our Office 365 group every month.

All of the files have the same structure, but different sheet names – all of the files only have 1 sheet and all of them have the same headers, but in a real case scenario the Power BI desktop and the process described here will work if you don’t have the same headers on all of the files.

Now, what we want to do is something that can be summarized in a series of steps like the following:

  1. Access all of the files from our site – we need to connect to our O365 group or SharePoint site and get the files first
  2. Combine all of the data from the files into one big table – once we connect to this data source, we need to be able to combine/consolidate/append all of the data from each of those excel files into one big table
  3. Clean and transform the data – ultimately, we want to unpivot some columns and also make the table more manageable to use with the Power BI Desktop and the DAX language.

In the end, we want the result of the combination to look similar to this:

Getting Data from SharePoint

First things first, we need to get the data and the Power BI Desktop makes it easier for us to get our data from either SharePoint or any O365 Group storage.

From the Get Data experience, let’s find the option for “SharePoint Folder”. This option will only require a text string which would be the URL for a SharePoint site.

Where can I find this so called url? Which one of the urls found in SharePoint should I use??

Good question! What the tool is expecting here is the root URL to the SharePoint site itself. Here’s a quick example:

My SharePoint site root url ishttps://powerbipanama.sharepoint.com/, but all of my files are actually in another site that starts with https://powerbipanama.sharepoint.com/sites/externalsales/ URL.

In order to use the correct URL, we need to be in the folder of the data that we’re trying to get and check the url that our browser shows. If it has the if it starts with the format of https://<site address>/sites/<sitename>/ then we need to use that url, otherwise we use the much simpler https://<site address>

In my own case, I’ll be using the https://powerbipanama.sharepoint.com/sites/externalsales url in order to connect to my site.

Once you input the url, you’ll then be asked for your credentials – be sure to select the “Organizational” type for your credentials and enter the same credentials that you use for your SharePoint site.

Now that we’re in, we’ll be able to see all of the files from all of the folders that we have access to in that specific site. This might be a pretty long list, so let’s do some filtering on the “Folder Path” column and select the folder where your data is being stored so you only get the files from it.

Filtering is key so you always get the files from the folder that you want. You can select multiple folders if you’d like as well.

We now have all of the files that we want to combine but, how do we combine these files?? What kind of magic is needed to consolidate all of them? Surely it can’t be just 1 click to combine them, right? Let’s find out.

New functionality! Combine Excel files with just 1 click

You might remember this popular posts from Microsoft itself on how to combine text and CSV files. It used to throw weird results with Excel files, but that changed with the latest version of the Power BI Desktop and Power Query.

This is now what happens after you click that button:

1. A new window will appear called ‘Combine Binaries’ – it will first find the first Binary from the table, which in our case is 03-March.xlsx, and it’ll create a sample function that will be applied to all of the other binaries.

Power bi get data from multiple excel sheets

We recommend always selecting the ‘Sample Binary Parameter’

In this window, you’ll see all of the objects found in the 03-March.xlsx file. If you select for example the 03-March spreadsheet, the same process will be applied to the rest of the binaries so Power Query/Power BI Desktop will extract the data from the 03-March spreadsheet from each file.

This is not our case. Each of our files has a different sheet name, so we recommend for these of cases to select the “Sample Binary Parameter” and click OK on this window.

2. Power Query / Power BI Desktop will do all the heavy lifting for you – you’ll end up with a table like the one we see in the next image where you can see all of the objects from each file. In our case, we see from the “Kind” column that all of our objects are simply sheets from the workbook and on the Name column we see the name of the workbook where each object comes from

After just 1 click, you have all of the data ready to be combined on the “Data” column. The elements within the red and orange outline were automatically created by the tool.

3. Expand the data found in the Data column – you’re one step away from consolidating all of your data! Just click the button with the double opposite arrow found on the “Data” column

Be sure to always click on “Load more” so you can expand the columns from all of your tables

At this point, we can check that our data has been consolidated in one table

YES!!!! All of the data from all of the 6 Excel files has been appended and now we have a table with all of this data.

Some more cleaning and transformation steps

After everything has been consolidated, we jump ahead and perform some cleaning and transformations shown within the outlined orange area

Here’s a quick list of the final steps that we did:

  • Removed Columns – we removed some unnecessary columns like the “Kind”, “Hidden” and “Item” column
  • Promoted Headers – afterexpandingthe data, we noticed that the first row had the column headers so we immediately promoted the first row to become headers of the table
  • Renamed Columns – we simply renamed some of the columnslike the “Name” column, which had the name of the product, to be named “Product Name”
  • Filtered Rows1 – since all of our files had headers, some of them were found in some rows, so we filter those out
  • Unpivot Other Columns – we used theunpivot feature on all the columns except the name of the month and the name of the product so we could get the “Attribute” and “Value” columns
  • Renamed Columns1 – we renamed the “Attribute” column to be “Market” and the “Value” column to be “Sales”
  • Sorted Rows – we sorted the data (ascending) by the Month column
  • Changed Type – we defined the data types for each of the columns and hit Close & Apply

The Result

Last but not least, get creative and create a report based on your consolidated data!

Of course, you could integrate this new table to an existing data model to add even more context to your sales performance and understand how you’re doing in each market.

New eBook Coming Soon

We would like to take this opportunity to tell you about an upcoming ebook for tips & tricks when working with the Get Data experience in the Power BI Desktop, a.k.a. Power Query.

The cool thing about this ebook? It’ll be completely FREE!!

-->

It's common to have data in multiple data sources. For example, you could have two databases, one for product information, and another for sales information. With Power BI Desktop, you can combine data from different sources to create interesting, compelling data analyses and visualizations.

In this tutorial, you combine data from two data sources:

  • An Excel workbook with product information
  • An OData feed containing orders data

You're going to import each dataset and do transformation and aggregation operations. Then, you'll use the two source's data to produce a sales analysis report with interactive visualizations. Later, you can apply these techniques to SQL Server queries, CSV files, and other data sources in Power BI Desktop.

Note

In Power BI Desktop, there are often a few ways to accomplish a task. For example, you can right-click or use a More options menu on a column or cell to see additional ribbon selections. Several alternate methods are described in the steps below.

Import Excel product data

First, import product data from the Products.xlsx Excel workbook into Power BI Desktop.

  1. Download the Products.xlsx Excel workbook and save it as Products.xlsx.

  2. Select the arrow next to Get Data in the Power BI Desktop ribbon's Home tab, and then select Excel from the Most Common menu.

    Note

    You can also select the Get Data item itself, or select Get Data from the Power BI Get started dialog box, then select Excel or File > Excel in the Get Data dialog box, and then select Connect.

  3. In the Open dialog box, navigate to and select the Products.xlsx file, and then select Open.

  4. In the Navigator, select the Products table and then select Transform Data.

A table preview opens in the Power Query Editor, where you can apply transformations to clean up the data.

Note

You can also open the Power Query Editor by selecting Edit Queries > Edit Queries from the Home ribbon in Power BI Desktop, or by right-clicking or choosing More options next to any query in the Report view, and selecting Edit Query.

Clean up the products columns

Your combined report will use the Excel workbook's ProductID, ProductName, QuantityPerUnit, and UnitsInStock columns. You can remove the other columns.

  1. In Power Query Editor, select the ProductID, ProductName, QuantityPerUnit, and UnitsInStock columns. You can use Ctrl to select more than one column, or Shift to select columns next to each other.

  2. Right-click any of the selected headers. Select Remove Other Columns from the drop-down menu.You can also select Remove Columns > Remove Other Columns from the Manage Columns group in the Home ribbon tab.

Import the OData feed's order data

Next, import the order data from the sample Northwind sales system OData feed.

  1. In Power Query Editor, select New Source and then, from the Most Common menu, select OData feed.

  2. In the OData feed dialog box, paste the Northwind OData feed URL, https://services.odata.org/V3/Northwind/Northwind.svc/. Select OK.

  3. In Navigator, select the Orders table, and then select Transform Data to load the data into Power Query Editor.

    Note

    In Navigator, you can select any table name, without selecting the checkbox, to see a preview.

Expand the order data

You can use table references to build queries when connecting to data sources with multiple tables, such as relational databases or the Northwind OData feed. The Orders table contains references to several related tables. You can use the expand operation to add the ProductID, UnitPrice, and Quantity columns from the related Order_Details table into the subject (Orders) table.

  1. Scroll to the right in the Orders table until you see the Order_Details column. It contains references to another table and not data.

  2. Select the Expand icon () in the Order_Details column header.

  3. In the drop-down menu:

    1. Select (Select All Columns) to clear all columns.

    2. Select ProductID, UnitPrice, and Quantity, and then select OK.

After you expand the Order_Details table, three new nested table columns replace the Order_Details column. There are new rows in the table for each order's added data.

Create a custom calculated column

Power Query Editor lets you create calculations and custom fields to enrich your data. You'll create a custom column that multiplies the unit price by item quantity to calculate the total price for each order's line item.

  1. In the Power Query Editor's Add Column ribbon tab, select Custom Column.

  2. In the Custom Column dialog box, type LineTotal in the New column name field.

  3. In the Custom column formula field after the =, enter [Order_Details.UnitPrice] * [Order_Details.Quantity]. You can also select the field names from the Available columns scroll box and select << Insert, instead of typing them.

  4. Select OK.

    The new LineTotal field appears as the last column in the Orders table.

Set the new field's data type

When Power Query Editor connects to data, it makes a best guess as to each field's data type for display purposes. A header icon indicates each field's assigned data type. You can also look under Data Type in the Home ribbon tab's Transform group.

Power Bi Get Data From Multiple Excel Files

Your new LineTotal column has an Any data type, but it has currency values. To assign a data type, right-click the LineTotal column header, select Change Type from the drop-down menu, and then select Fixed decimal number.

Note

You can also select the LineTotal column, then select the arrow next to Data Type in the Transform area of the Home ribbon tab, and then select Fixed decimal number.

Clean up the orders columns

To make your model easier to work with in reports, you can delete, rename, and reorder some columns.

Your report is going to use the following columns:

  • OrderDate
  • ShipCity
  • ShipCountry
  • Order_Details.ProductID
  • Order_Details.UnitPrice
  • Order_Details.Quantity
  • LineTotal

Select these columns and use Remove Other Columns as you did with the Excel data. Or, you can select the non-listed columns, right-click on one of them, and select Remove Columns.

You can rename the columns prefixed with 'Order_Details.' to make them easier to read:

  1. Double-click or tap and hold each column header, or right-click the column header, and select Rename from the drop-down menu.

  2. Delete the Order_Details. prefix from each name.

Finally, to make the LineTotal column easier to access, drag and drop it to the left, just to the right of the ShipCountry column.

Review the query steps

Your Power Query Editor actions to shape and transform data are recorded. Each action appears on the right in the Query Settings pane under Applied Steps. You can step back through the Applied Steps to review your steps, and edit, delete, or rearrange them if necessary. However, changing preceding steps is risky as that can break later steps.

Select each of your queries in the Queries list on the left side of Power Query Editor, and review the Applied Steps in Query Settings. After applying the previous data transformations, the Applied Steps for your two queries should look like this:

Tip

Underlying the Applied Steps are formulas written in the Power Query Language, also known as the M language. To see and edit the formulas, select Advanced Editor in the Query group of the Home tab of the ribbon.

Import the transformed queries

When you're satisfied with your transformed data and ready to import it into Power BI Desktop Report view, select Close & Apply > Close & Apply in the Home ribbon tab's Close group.

Once the data is loaded, the queries appear in the Fields list in the Power BI Desktop Report view.

Manage the relationship between the datasets

Power BI Desktop doesn't require you to combine queries to report on them. However, you can use the relationships between datasets, based on common fields, to extend, and enrich your reports. Power BI Desktop may detect relationships automatically, or you can create them in the Power BI Desktop Manage Relationships dialog box. For more information, see Create and manage relationships in Power BI Desktop.

The shared ProductID field creates a relationship between this tutorial's Orders and Products datasets.

  1. In Power BI Desktop Report view, select Manage Relationships in the Home ribbon tab's Relationships area.

  2. In the Manage relationships dialog box, you can see that Power BI Desktop has already detected and listed an active relationship between the Products and Orders tables. To view the relationship, select Edit.

    Edit Relationship opens, showing details about the relationship.

  3. Power BI Desktop has autodetected the relationship correctly, so you can select Cancel and then Close.

In Power BI Desktop, on the left side, select Model to view and manage query relationships. Double-click the arrow on the line connecting the two queries to open the Edit relationship dialog and view or change the relationship.

To get back to Report view from Model view, select the Report icon.

Create visualizations using your data

Power bi get data from multiple excel files online

You can create different visualizations in Power BI Desktop Review View to gain data insights. Reports can have multiple pages, and each page can have multiple visuals. You and others can interact with your visualizations to help analyze and understand data. For more information, see Interact with a report in Editing view in Power BI service.

You can use both of your data sets, and the relationship between them, to help visualize and analyze your sales data.

First, create a stacked column chart that uses fields from both queries to show the quantity of each product ordered.

Power Bi Get Data From Multiple Excel Files
  1. Select the Quantity field from Orders in the Fields pane at the right, or drag it onto a blank space on the canvas. A stacked column chart is created showing the total quantity of all products ordered.

  2. To show the quantity of each product ordered, select ProductName from Products in the Fields pane, or drag it onto the chart.

  3. To sort the products by most to least ordered, select the More options ellipsis (...) at the visualization's upper right, and then select Sort By Quantity.

  4. Use the handles at the corners of the chart to enlarge it so more product names are visible.

Next, create a chart showing order dollar amounts (LineTotal) over time (OrderDate).

  1. With nothing selected on the canvas, select LineTotal from Orders in the Fields pane, or drag it to a blank space on the canvas. The stacked column chart shows the total dollar amount of all orders.

  2. Select the stacked chart, then select OrderDate from Orders, or drag it onto the chart. The chart now shows line totals for each order date.

  3. Drag the corners to resize the visualization and see more data.

    Tip

    If you only see Years on the chart and only three data points, select the arrow next to OrderDate in the Axis field of the Visualizations pane, and select OrderDate instead of Date Hierarchy.

Finally, create a map visualization showing order amounts from each country.

  1. With nothing selected on the canvas, select ShipCountry from Orders in the Fields pane, or drag it to a blank space on the canvas. Power BI Desktop detects that the data is country names. It then automatically creates a map visualization, with a data point for each country with orders.

  2. To make the data point sizes reflect each country's order amounts, drag the LineTotal field onto the map. You can also drag it to Drag data fields here under Size in the Visualizations pane. The sizes of the circles on the map now reflect the dollar amounts of the orders from each country.

Power Bi Get Data From Multiple Excel Sheets

Interact with your report visuals to analyze further

In Power BI Desktop, you can interact with visuals that cross-highlight and filter each other to uncover further trends. For more information, see Filters and highlighting in Power BI reports.

Because of the relationship between your queries, interactions with one visualization affect all the other visualizations on the page.

On the map visualization, select the circle centered in Canada. The other two visualizations filter to highlight the Canadian line totals and order quantities.

Select a Quantity by ProductName chart product to see the map and the date chart filter to reflect that product's data. Select a LineTotal by OrderDate chart date to see the map and the product chart filter to show that date's data.

Tip

To deselect a selection, select it again, or select one of the other visualizations.

Complete the sales analysis report

Power Bi Get Data From Multiple Excel Files One

Your completed report combines data from the Products.xlsx Excel file and the Northwind OData feed in visuals that help you analyze different countries' order information, time frames, and products. When your report is ready, you can upload it to Power BI service to share it with other Power BI users.

Power Bi Get Data From Multiple Excel Files Without

Next steps