It currently consists of 3 Excel Files and Dave’s expecting a lot more!Īs you can see the number of sheets and the naming convention is different across files and there are sheets that need to be excluded from consolidation – like Summary and Notes All the action is going to happen on 2 Sheets – SET PARAMETERS and DATAĭave’s data is in Folder Input6. Also, under Data – Get Data – Query Options – Current Workbook – Privacy choose – the 2nd option “Ignore….”.
Store the UniversalDC.xlsm on your Desktop – Open it – Click on Enable content. A Power BI Desktop version is also provided that has a slightly different interface and functionality. The Excel Version of the Universal DC is used in the illustrations in the blog post.
The data is fictious and is used to illustrate different scenarios that Universal DC can handle. You should see folders named Input1 …Input 10
#Universal database update zip archive#
His go to tool in the past has been the From Folder connector in Power Query that has served him well and saved him hundreds of hours already.īut one bright Monday morning Dave gets a curve ball thrown at him that needs something a bit more powerful than the out of the box experience that Power Query offers.Ī tool that can consolidate data but is independent of Sheet Names, Column Names, Order of columns, can include/exclude sheets, files and allow users to specify folder path, file type, start point of data or determine it automagically, all this without having to fire up the Power Query editor!īefore we start looking at the scenario, download the attachments.Įxtract the Data Files from the zip archive in a folder called Data on your C: Drive (you can change the folder path later). Some are system exports (CSV’s, Other Delimited Text), others could be manually maintained Excel tables or tables in PDF’s I was pleasantly surprised when they agreed and here, we are! The Problemĭave the Datasmith frequently consolidates Data from a variety of sources. Introductionĭuring an interaction with the Power Query team, I mentioned about the lack of articles on this blog and enquired if they would be open to me doing a guest post. You can read more here on LinkedIn and on the sheet About of the Universal Data Consolidator file. He has a Degree in Electrical Engineering. He delivers Trainings on Power BI, Excel, VBA and has been developing applications using these technologies for his Clients since 2007. Please reach out to your Microsoft contact if you are interested in contributing to the Power Query Blog.Īuthor: Sameer Bhide (Sam) currently works as a freelance Consultant and Trainer. This article was authored by Sameer Bhide, a community author.
#Universal database update zip windows#
This content applies to: Excel for Windows
0 kommentar(er)
