![Power-BI-Content-Article.png](https://quantinsightsnetwork.com/wp-content/uploads/2024/04/Power-BI-Content-Article-678x381.png)
This tutorial will discuss about how to import and open an Excel file with multiple sheets to one Power BI table.
This data Excel file will be used for this tutorial.
![1-17 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/1-17-1024x486.jpg)
![1-17 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/1-17-1024x486.jpg)
The file contains 60 sheets of data with the same layout.
![2-15 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/2-15.jpg)
![2-15 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/2-15.jpg)
The objective of this tutorial is to load all the Excel sheets to Power BI and retrieve the Bond information as one table. You need to get the Security ticker and add that in a separate column. Then, append all of them together in a single table that’s leveraging the UI to generate the code that you need.
Importing The Excel Sheets To Power BI
The first thing to do is open the Power Query Editor and create a parameter to store the file location using the Manage Parameters option.
![3-16 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/3-16.jpg)
![3-16 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/3-16.jpg)
Inside the Manage Parameters box, click New and input FileLocation as the parameter name. Next, set the Data Type to Text and Suggested Values to Any Value. Copy the file path of the Excel file and paste it in the Current Value fields.
![4-14 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/4-14.jpg)
![4-14 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/4-14.jpg)
After pressing OK, click New Source and select Excel Workbook.
![5-14 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/5-14.jpg)
![5-14 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/5-14.jpg)
Next, select and open the Excel File. Once done, the Navigator screen will open and list all the sheets within the Excel file.
![6-14 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/6-14-1024x858.jpg)
![6-14 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/6-14-1024x858.jpg)
Select the first sheet and press OK. After that, you’ll then see the table inside Power Query Editor.
![7-14 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/7-14.jpg)
![7-14 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/7-14.jpg)
The next thing to do is change the hard-coded file path into the file parameter. Open the Advanced Editor window and change the file path in the Source code to FileLocation.
![8-12 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/8-12-1024x168.jpg)
![8-12 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/8-12-1024x168.jpg)
It’s best to have your data in Excel tables because the boundaries or the data range are defined before you bring them into Power Query. Since the file contains sheets and no tables, it runs the risk of bringing in empty columns and empty rows so you need to sort that out.
Bringing Out The Security Ticker From Excel To Power BI
Knowing that the layout of the sheets is fixed helps in building a solution, especially if you want to maximize and use the user interface to generate the code. For example, one requirement is to add a column that contains the Security ticker. If you look at the data, you can see the ticker.
![9-13 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/9-13-1024x390.jpg)
![9-13 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/9-13-1024x390.jpg)
The user interface can help you extract the value of the ticker. Right-click on the cell and select Add As New Query.
![10-12 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/10-12.jpg)
![10-12 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/10-12.jpg)
In the formula bar, you’ll see that the table name is followed by a zero-based row number between curly brackets. The curly brackets are called positional index operators. You’ll also see the field name enclosed by square brackets which are called field access operators.
![11-13 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/11-13.jpg)
![11-13 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/11-13.jpg)
With those codes, you can now extract the value. Go back to the Bond table and get the Bond data. First, remove the top 8 eight rows. Click Remove Rows and select Remove Top Rows.
![12-13 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/12-13.jpg)
![12-13 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/12-13.jpg)
Next, input 8 in the Number Of Rows field and press OK.
![13-13 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/13-13.jpg)
![13-13 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/13-13.jpg)
Then, click Use First Row As Headers to set the headers.
![14-12 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/14-12.jpg)
![14-12 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/14-12.jpg)
Once done, the Bond table will now look like this.
![15-10 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/15-10.jpg)
![15-10 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/15-10.jpg)
Directly below the header, you’ll see a thin line that represents the column quality. From there, you can see that there are quite a number of blanks in the columns. This means that the file brought in a lot of empty rows.
![16-11 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/16-11.jpg)
![16-11 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/16-11.jpg)
Removing Empty Rows
To remove the empty rows, click Remove Rows and select Remove Blank Rows.
![17-10 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/17-10.jpg)
![17-10 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/17-10.jpg)
That transformation generates this syntax:
![18-10 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/18-10-1024x38.jpg)
![18-10 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/18-10-1024x38.jpg)
Record.FieldValues is getting all the values from the current row in the table as a list. List.RemoveMatchingItems removes all of the values in the first list that has a match in the second list. The second list only contains an empty text string or a null. These are the values that will be excluded from the first list.
If all the empty text strings and null have been removed from the list with the record field values, the list should be empty and the List.IsEmpty will evaluate to True. Then, Table.SelectRows will retain the Trues.
You should not end up with a table with only blank rows. That’s why the keyword not is added before the List.IsEmpty. That then returns a table containing non-blank rows.
Aside from blank rows, you also need to remove blank columns. But before that, take a look at what Power Query generates when you remove a column. Select the fourth column and click Remove columns.
![19-9 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/19-9.jpg)
![19-9 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/19-9.jpg)
After performing the transformation, you’ll see this syntax in the formula bar.
![20-9 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/20-9.jpg)
![20-9 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/20-9.jpg)
It calls the Table.RemoveColumns function then references and passes the previous step in the Applied Steps pane as first argument. The transformation further passes a list containing the column name of the columns that you want to remove.
Duplicating A Query
Now, duplicate the query and select the Promote Headers step in the Applied Steps pane. Next, right-click on that step and select Delete Until End.
![21-9 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/21-9.jpg)
![21-9 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/21-9.jpg)
Remember that you can use the positional index operator to pass a zero-based row number in a set of curly brackets. So keep only the first row by inputting 0 inside two curly brackets in the formula bar.
![22-9 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/22-9.jpg)
![22-9 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/22-9.jpg)
Once done, the Bond table will look like this.
![23-9 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/23-9.jpg)
![23-9 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/23-9.jpg)
From there, reuse a part of the logic that was created by the Removed Blank Rows step to turn the record into a list and to remove the null. Go back to the Bond query and select the Remove Blank Rows step. Next, copy this M code.
![24-7 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/24-7.jpg)
![24-7 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/24-7.jpg)
Go back to the duplicated query and paste the code in the formula bar. Then, arrange some strings to make the formula look like this.
![25-7 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/25-7.jpg)
![25-7 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/25-7.jpg)
You have now created a list with column names that you want to keep. Rename the query Column Names.
![26-8 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/26-8.jpg)
![26-8 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/26-8.jpg)
Then, go back to the Bond query. Since you’ve created a query that contains all of the columns you want to keep, you need to list all columns that you want to exclude in the Table.RemoveColumns function.
![27-6 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/27-6.jpg)
![27-6 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/27-6.jpg)
Change {Column4} with the same syntax that was copied from the Removed Blank Rows steps. Change also {“”, null} to ColumnNames.
![28-7 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/28-7.jpg)
![28-7 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/28-7.jpg)
Then, you need to pass a list with the actual column names from the Bond table. Change the Record.FieldValues(_) to Table.ColumnNames(). Input #”Removed Blank Rows” inside the parentheses to pass a table reference argument.
![29-6 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/29-6-1024x37.jpg)
![29-6 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/29-6-1024x37.jpg)
Assigning Data Types To Columns
The next thing to do is assign appropriate data types to the columns. For the Date column, click the icon beside the header and select Date.
![30-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/30-5.jpg)
![30-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/30-5.jpg)
For the PX_LAST and YLD_YTM_MID columns, select the Decimal Number data type.
![31-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/31-5.jpg)
![31-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/31-5.jpg)
With these three queries, you’ve created all of the building blocks that you can use to design a solution that addresses all of the sheets in the Excel file. For that, you need to duplicate the Bond query and delete all the steps except the Source step in the Applied Steps pane.
Adding Security Ticker Column From Excel To Power BI
In the Source step, you can see all of the data inside the Excel file. Instead of creating a separate query for each sheet, you can use the Bond query and transform the nested tables in the Data column.
First, add the Security ticker. If you click the white space inside the cell of any table, you’ll see a preview of the contents of that table.
![32-5 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/32-5.jpg)
![32-5 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/32-5.jpg)
You need to create a logic using an IF statement to get the Security ticker. Go to the Column2 query and copy the address of the Security ticker from the formula bar.
![33-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/33-5.jpg)
![33-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/33-5.jpg)
Next, go back to the Bond query and add a custom column.
![34-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/34-5.jpg)
![34-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/34-5.jpg)
Name the column Security Ticker and write the following M code.
![35-4 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/35-4.jpg)
![35-4 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/35-4.jpg)
The formula has an IF statement saying that if the word Security is found in Column1, it will provide the value of the cell from Column2 which is adjacent to it. Otherwise, a null will be provided.
After pressing OK, a new column with the Security tickers will be added to the table.
![36-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/36-5.jpg)
![36-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/36-5.jpg)
Click the drop-down button of the Security Ticker column and deselect null to remove all nulls in the column.
![37-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/37-5.jpg)
![37-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/37-5.jpg)
After that, you will be left with all of the Bonds’ information from all of the sheets. All you need to do is repeat the performed transformations done in the Bond(2) query and apply them to the nested tables in the Data column.
Creating A Custom Column For Bond Query
Go to the Bond(2) query, open the Advanced Editor window, and copy the following code:
![38-6 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/38-6-1024x254.jpg)
![38-6 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/38-6-1024x254.jpg)
Next, go back to the Bond query and create another custom column. Since you need to apply multiple transformations across multiple steps, you have to use a let statement. So, input let and paste the code from the Advanced Editor.
![39-5 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/39-5.jpg)
![39-5 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/39-5.jpg)
Then, change the Bond_Sheet to Data to transform the table in the Data column.
![40-4 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/40-4.jpg)
![40-4 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/40-4.jpg)
After pressing OK, a new column will be added in the table.
![41-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/41-4.jpg)
![41-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/41-4.jpg)
If you click the white space inside any cell of that column, you’ll see the data from the Bond(2) query.
![42-4 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/42-4.jpg)
![42-4 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/42-4.jpg)
All you need is in the last two columns of the Bond query. So, select both the Security Ticker and Custom columns and click Remove Other Columns from the Remove Columns ribbon.
![43-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/43-4.jpg)
![43-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/43-4.jpg)
Expand the Custom column and uncheck the Use The Original Name As Prefix box.
![44-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/44-4.jpg)
![44-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/44-4.jpg)
Once done, validate the table. Below the table, select the Column Profiling Based On Entire Data Set option.
![45-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/45-4.jpg)
![45-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/45-4.jpg)
After that transformation, an error will occur in the Data column.
![46-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/46-5.jpg)
![46-5 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/46-5.jpg)
So examine the error by clicking the Data column and selecting Keep Errors in the Keep Rows ribbon.
![47-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/47-4.jpg)
![47-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/47-4.jpg)
If you click the Error value inside the Data column, you can read the error message below:
![48-3 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/48-3.jpg)
![48-3 | Master Data Skills + AI excel to Power BI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/48-3.jpg)
To solve the issues, first remove the Kept Errors step. Click the Data column and select Remove Errors from the Remove Rows ribbon.
![49-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/49-4.jpg)
![49-4 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/49-4.jpg)
Then, set the column profiling option back to the top 1000 rows. And that’s it!
![50-3 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/50-3.jpg)
![50-3 | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/11/50-3.jpg)
Conclusion
This user interface-driven solution can help you append multiple sheets from an Excel file to Power BI. Instead of creating 60 separate queries and performing all transformations repeatedly, this solution can create a single query that performs all of the transformations. Utilize and maximize this solution to build a great data report.
Melissa
[youtube https://www.youtube.com/watch?v=SzOk1xjx2z4&w=784&h=441]
***** Related Links *****
Power Query Tutorial: How To Split Multi-Lined Excel Cells In Power BI
Power BI And Excel: Difference In DAX Formulas
Using Power Query Advanced Editor To Extract Values Before A Specific Text
***** Related Course Modules *****
Power Query Series
Ultimate Beginners Guide To Power BI
DAX Formula Patterns
***** Related Support Forum Posts *****
Ability To Cut Data Power BI Vs Excel
Power BI With Excel Merge Cells
Power BI Desktop Crashing On Load From Excel
For more excel to Power BI queries to review see here….
Be the first to comment