Forum Discussion
Consolidating nutrition and price data from multiple sources into one Excel dashboard
I maintain a single Excel workbook that tracks breakfast-menu items calories, macros, allergens, and local prices pulled from several public sources like https://mcd-breakfast-menu.com/. Each source formats its table differently: some list energy in kcal and others in kJ; decimal separators flip between commas and periods; prices appear in four currencies. After each monthly update I hand-copy tables into a Raw sheet and try to normalise them with Power Query and a few LET and LAMBDA functions, but one extra column or unit change breaks my mapping. Has anyone built a durable workflow for problems like this? I am looking for ideas on (1) keeping unit conversions transparent so a non-Excel colleague can audit the maths, (2) alerting me when a source table adds or removes a column so the model does not shift silently, and (3) deciding whether to keep all cleanup in Power Query or move some logic into hidden staging sheets. Any real-world templates or processes would be welcome.
1 Reply
When you are pulling from sources that you don't have any control over and no guarantees from, there is no way to make sure something they do won't break the process. The best you can do is try to detect changes and give warnings. For example if you pull in that first table from the McD's site you will expect to have the following headers:
Items Calories Protein Total Carbs Total Fat so you can check for those headers and output an error if that isn't what you received.
i would recommend one or more extra tabs for things like conversions, configurations, and settings and you can have tables for currency convresion and unit conversions and such to make it easier to see and use.
but in the end you are looking at complicated power query and/or lambda functions so anyone else inheriting it will need to be relatively savvy in excel to understand how it is working.
