Forum Discussion
Consolidating data from different sources
Thanks for the response. So the current monthly sales report comes from a different system and although I can pull multiple months I run additional analysis within the master document to show customer trends, month by month analysis etc. Importing just the most recent months data seemed the easiest way to increment the data without having to double check a whole load of the analytics formulae we use.
The customer contact data is really to be used so we can run campaigns based on the results. So for example, if I can see a customer purchases every 3 months, I can run a campaign to target them in 2 months time.
At the moment I would have to export the customerID list, then do a consolidate with dataset1 and then run a mailmerge (for example) of the results. If I can merge the datasets I just reduce about 5 steps in my current process.
Hope that makes sense?
If your monthly files have the same structure you may combine them with Power Query keeping in one folder, merge with Dataset1, add other transformation of needed and return result into the Master file. Here make analysis on consolidated data, preferably through data model. After that the process of updating will be adding in another sales file to the folder and clicking on Refresh All in Master file.
SergeiBaklan Thanks, might have to do a crash course in Power Query. I have looked at it for other projects but not had the time/determination to really get stuck in.
Paul_icapture_app Yes, learning of Power Query is never ending process (as any other technology), but even after some initial investment of time you could start easy to do some tasks which are really complex if use formulas.
SergeiBaklan Sadly, Power Query is not available (so far as I can tell) on Excel for the Mac.......which is why I still rely on the D_____ database functions.
