Forum Discussion
Power Query - return formula vs value to Excel table
I am using Power Query (Excel 2013) to gather sales and forecasting data. The resulting Excel table is then used by Sales to update the forecast. When forecast units are updated I need the resulting sales $ to update automatically. Unfortunately, the units, $/unit and sales $ are in rows, so I can not add a column to calculate the Sales $.
I have written a VBA routine that goes through the table and replaces all the forecast Sales $ with a formulae (simple for individual items, a little more complex to sum customers and product lines).
Is there a way in PQ, to return a formula to the excel table instead of value?
2 Replies
Hi Ken,
PQ doesn't return formulas. I didn't catch what's your data structure, but why don't you do all transformations and updates by PQ, perhaps with help of Power Pivot? Most probably that's possible.
That was my guess, but figured I should ask.
FYI - I have used a pivot table to update, but that goes to another tab and does not provide instant updates when many changes and tweeks are being made to the forecast units.
I'll keep working on my VBA routine.
Thank you.
