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 3 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.
What I plan to do is filter out the unique accounts and see how many gold, silver and bronze customers there are.
In this case, the next thing we want to do is read in another file that contains the customer status by account.
You can think of this as a company’s customer segmentation strategy or some other mechanism for identifying their customers. This account number was not in our status file, so we have a bunch of Na N’s.
This article will walk through the basic flow required to parse multiple Excel files, combine the data, clean it up and analyze it.
The combination of python pandas can be extremely powerful for these activities and can be a very useful alternative to the manual processes or painful Before, I get into the examples, here is a simple diagram showing the challenges with the common process used in businesses all over the world to consolidate data from multiple Excel files, clean it up and perform some analysis.
This option will only require a text string which would be the URL for a Share Point site. Which one of the urls found in Share Point should I use?? What the tool is expecting here is the root URL to the Share Point site itself.