Reasons for Uploading Excel Reports to Power Bi
Excel and Power BI are key for information analysts, though Excel is often where people start and stop with exploring data assay tools. By learning to employ Excel with Power BI, you can go further with your data and create sustainable solutions for you and your organization.
This is the first role of the Ability BI x Excel blog series, where we will look at the different ways of connecting to Excel files, how to sustain future data, and automate the transformation.
We volition as well wait at some other useful functionalities that involve Power BI and Excel and eventually touch on common data modeling transformation that 1 would need to clarify data that lives in Excel better.
Sections:
- Your File Location
- Let's Import!
- Import Table or Sheet?
- To Load or to Transform?
- Import a Sail with Multiple Tables
- Power BI Tin't Find my Excel File!
Your File Location
If you take your Excel file on your computer hard drive, the get-go thing to call back near before importing the information is, has the file has establish its forever home in the correct binder and location?
If not, let's move the file to the right folder and location before importing information technology in Power BI. It is important to accept a permanent location and not move the file later on you take imported it.
In Power BI Can't Find my Excel File, we will await at what happens if we move the Excel file after importing it into Power BI, and how we can solve the issue.
Let's Import!
At present that your file has a dwelling house, allow's import the data into Power BI.
In Power BI Desktop, clickGet data and selectExcel in the list of common data sources, or just click the icon that has the "x" in a green box.
TheOpen window will pop upwards and you can navigate to your Excel file. For this demo, I will utilise theUs Salesfile.
In theNavigator window, you will be able to meet the file you have selected and two data import options that you tin select,SalesTable andSales Sheet. These two options may accept the same preview when you click on one of them, but they are actually in a different format, as indicated past the icon side by side to it, Table or Sheet.
Import Tabular array or Sheet?
SalesTable has a table icon with a blue header, which indicates that it is a table, whileSales Sheet has the sheet icon. Permit'southward take a peek into Excel to see where these are and how are they different.
A canvas is denoted by the tab at the bottom left of the Excel window. To notice out the name of a table, select any jail cell in the table, and clickTable Pattern in the ribbon. The difference betwixt a canvass and a tabular array is that a sheet can take multiple tables. Additionally, a table can't have spaces in the name, this explains why the proper name of the tabular array nosotros are using isSalesTable, instead ofSalesTable.
Going back to our question, do we select a canvas (Sales Sheet) or a table (SalesTable) in the Navigator window? In my opinion, a table is the better choice. One time you checked the table, the next question is toLoad or toTransformData?
In Import a Canvas with Multiple Tables, I will demonstrate what happens when we have a canvass that has multiple tables.
To Load or to Transform?
If you cull Load, Ability BI desktop volition connect to your Excel file and make a copy of (import) all the data in the selected table, and immediately shop it in the Power BI data model of your current pbix file.
If y'all cull Transform Data, you will have the option to perform transformation and data cleansing inPower Query Editor before importing the data into your Power BI data model.
Either manner, you will still have the option to render to Power Query Editor in one case you have imported the information into the Power BI data model by clickingTransform Data in Power BI Desktop.
Struggling with your analytics journey?
Let Iteration Insights get you on the right path, view our services to see how we can assist you today.
Import a Sheet with Multiple Tables
If your sheet has merely 1 table, importing the sheet volition bring the same event as importing a table. But if your sheet has multiple tables, the imported data in Power BI will be a combination of all the tables in the sheet.
What does that mean? I am going to add another canvas namedCountry Sheet that has two tables (CountryCode andCountryInitial)into the Excel file and import information technology into Power BI.
The kickoff affair we will see is more than options for sheets and tables. The canvas I just added in the Excel file is denoted by the ruby-red box in the above picture while the tables that are in theCountry Sheet are as well visible in the blueish box (CountryCode andCountryInitial).
I will select theCountry Sheet and select Load. Here is the imported result in Ability BI:
Power BI will take all data that is bachelor in a canvas and combine the data into i single table. That is how the blankColumn3 came near merely this may not be the result we are looking for. Thus selecting a tabular array instead of a sheet in theNavigator will be a more accurate way to become the right data in this example.
If you lot have multiple tables in a canvass, and theNavigator was not able to identify the split up tables, y'all may need to convert the separate tables in the original Excel file into tabular array format by selecting the relevant cells and hitting Ctrl-T.
Ability BI Tin't Find my Excel File!
If you accidentally or intentionally move your Excel file into a different folder or change the name of your Excel file, you may encounter the result where Power BI is non able to connect to your Excel file.
This fault volition ordinarily announced if you striking refresh on your Power BI file or when you open the Ability Query Editor. Only don't worry! We can ready this hands if you know the new location of the file.
In Power BI Desktop, go to the ribbon, and selectData source settings. In the pop-upward window, select the data source (i.e. the Excel file) that needs to exist redirected and clickAlter Source.
In theExcel popular-up window, clickBrowse and navigate to the new file location. ClickOK and your file will be connected again to your Power BI report.
I hope this piffling tutorial volition help y'all become started with Excel and Power BI. Stay tuned for more blogs in this Power BI x Excel serial. Exercise you have other questions or recommendations?! Allow usa know in the comment department below!
Source: https://iterationinsights.com/article/import-excel-to-power-bi/
0 Response to "Reasons for Uploading Excel Reports to Power Bi"
Post a Comment