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.Access all of the files from our site – we need to connect to our O365 group or SharePoint site and get the files first.
#EXCEL GET DATA FROM SHAREPOINT FOLDER SERIES#
Now, what we want to do is something that can be summarized in a series of steps like the following: 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.Īll 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. Here’s a few remarks about our case:Įach 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 files are provided by a 3 rd party in order to give us a better understanding of the whole market and how well some products are doing on each market.
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. 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 3 rdparty 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. I thinks it’s very interesting use of Power BI and Sahrepoint and i suggest you to read it: This post was create by Ken Pulse and Miguel Escobar and published on power bi Blog.