en en ms fi fi fi ms fi fi fi en fi ms hbs_lat ms sl ms fi fi fi ms ms fi fi it fi en fi fi en fi fi fi fi fi fi fi fi fi fi fi fi fi fi sq fi en fi fi fi fi fi eu fi fi fi de id fi fi fi fi fi fi fi fi fi fi fi fi fi tr fi fi fi ms en en it ru fi en en hbs_lat en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en fi fi fi fi fi fi fi fi fi fi fi fi fi fi ca en en id en en en en en en en en ms ms ms en en ml fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi fi en fi ms en en en fi vo fi fi fi fi fi ms sl fi fi en fi fi fi fi de fi fi id fi en fi fi sv fi fi fi hbs_lat da de de de el de en en tr en en en en en en en es es fi fr fr fr fr hu it it ja ko nb nl nl pl pt pt ru sv th tr zh zh ar he
Combining Excel Files hosted on a SharePoint folder with Power BI | Microsoft Power BI – blogi | Microsoft Power BI
Office 365 + Power BI
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.
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 data – we’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:
Access all of the files from our site – we need to connect to our O365 group or SharePoint site and get the files first
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
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 is https://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:// /sites/ / then we need to use that url, otherwise we use the much simpler https://
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.
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 – after expanding the 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 columns like 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 the unpivot 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
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!!
Visit our website to get more information about the ebook and how to get it.
Tilaa Power BI -uutiskirje
Rekisteröidy alla, niin saat uusimmat Power BI -tiedot suoraan Saapuneet-kansioon.
Maa Afganistan Ahvenanmaa Alankomaat Albania Algeria Amerikan Samoa Andorra Angola Anguilla Antarktis Antigua ja Barbuda Argentiina Armenia Aruba Australia Azerbaidžan Bahamasaaret Bahrain Bangladesh Barbados Belgia Belize Benin Bermuda Bhutan Bolivia Bonaire Bosnia ja Hertsegovina Botswana Bouvet'nsaari Brasilia Brittiläinen Intian valtameren alue Brittiläiset Neitsytsaaret Brunei Bulgaria Burkina Faso Burundi Cabo Verde Caymansaaret Chile Cookinsaaret Costa Rica Côte d'Ivoire Curaçao Djibouti Dominica Dominikaaninen tasavalta Ecuador Egypti El Salvador Eritrea Espanja Etelä-Afrikka Etelä-Georgia ja Eteläiset Sandwichsaaret Etelä-Sudan Etiopia Falklandinsaaret Fidži Filippiinit Färsaaret Gabon Gambia Georgia Ghana Gibraltar Grenada Grönlanti Guadeloupe Guam Guatemala Guernsey Guinea Guinea-Bissau Guyana Haiti Heard ja McDonaldinsaaret Honduras Hongkong, erityishallintoalue Huippuvuoret Indonesia Intia Iran Irlanti Islanti Israel Italia Itävalta Jamaika Jan Mayen Japani Jemen Jerseyn voutikunta Jordania Joulusaari Kambodža Kamerun Kanada Kazakstan Kenia Keski-Afrikan tasavalta Kiina Kirgisia Kiribati Kolumbia Komorit Kongo Kongon demokraattinen tasavalta Kookossaaret Korean tasavalta Kosovo Kreikka Kroatia Kuwait Kypros Laos Latvia Lesotho Libanon Liberia Liechtenstein Liettua Luxemburg Macao, erityishallintoalue Madagaskar Malawi Malediivit Malesia Mali Malta Mansaari Marokko Marshallinsaaret Martinique Mauritania Mauritius Mayotte Meksiko Mikronesia Moldova Monaco Mongolia Montenegro Montserrat Mosambik Namibia Nauru Nepal Nicaragua Niger Nigeria Niue Norfolkinsaari Norja Oman Pakistan Palau Palestiinalaishallinto Panama Papua-Uusi-Guinea Paraguay Peru Pitcairn Pohjois-Makedonia Pohjois-Mariaanit Portugali Puerto Rico Puola Päiväntasaajan Guinea Qatar Ranska Ranskan eteläiset alueet Ranskan Guayana Ranskan Polynesia Réunion Romania Ruanda Ruotsi Saba Saint Barthélemy Saint Kitts ja Nevis Saint Lucia Saint Martin Saint Vincent ja Grenadiinit Saint-Pierre ja Miquelon Saksa Salomonsaaret Sambia Samoa San Marino São Tomé ja Príncipe Saudi-Arabia Senegal Serbia Seychellit Sierra Leone Singapore Sint Eustatius Sint Maarten Slovakia Slovenia Somalia Sri Lanka St. Helena, Ascension, Tristan da Cunha Sudan Suomi Suriname Swazimaa Sveitsi Syyria Tadžikistan Taiwan Tansania Tanska Thaimaa Timor-Leste Togo Tokelau Tonga Trinidad ja Tobago Tšad Tšekin tasavalta Tunisia Turkki Turkmenistan Turks- ja Caicossaaret Tuvalu Uganda Ukraina Unkari Uruguay Uusi-Kaledonia Uusi-Seelanti Uzbekistan Wallis ja Futuna Vanuatu Vatikaanivaltio Venezuela Vietnam Viro Yhdistyneet arabiemiirikunnat Yhdistynyt kuningaskunta Yhdysvallat Yhdysvaltain Neitsytsaaret Yhdysvaltain pienet erillissaaret Zimbabwe
Haluan saada seuraavan Power BI -uutiskirjeen. Tietosuojatiedot.
Osallistuminen edellyttää henkilökohtaisten tietojesi siirtämistä muihin maihin, joissa Microsoft toimii, muun muassa Yhdysvaltoihin. Lähettämällä tämän lomakkeen suostut tietojesi siirtämiseen Kiinan ulkopuolelle. Tietosuojalauseke.
Mikä on Power BI?
Power BI on yritysanalytiikan työkalupaketti tietojen analysointiin ja merkityksellisten tietojen jakamiseen. Seuraa liiketoimintaa ja hanki nopeasti vastauksia monipuolisista raporttinäkymistä, joita voi käyttää kaikissa laitteissa.
Hanki viimeisimmät uutiset Power BI -blogista
Tilaa sähköpostitse Sinun on annettava kelvollinen sähköpostiosoite Olet antanut henkilökohtaisen sähköpostiosoitteen. Anna työpaikan tai oppilaitoksen sähköpostiosoite. Tapahtui virhe. Yritä myöhemmin uudelleen.
Näytä kaikki ladattavat tiedostot