Nov 10 2020 02:42 AM
Nov 10 2020 02:42 AM
I am making a business case and are unsure about the best general approach: "basic" tables and formulas with accompanying "simple" charts, Pivot tables and Pivot Charts, Power Query, Power Pivot, and/or a mix of all those?
The setup is this: I have extensive sales data 2015-2020 in .xls format exported from an SQL database, and now have to project scenarios for the future sales of coming products 2020-2025, in order to decide on pricing, calculate Return on Investment times, etc.
So on the one hand I have a big table of fixed data, well suited for Power Query and Power Pivot. But on the other hand I have a set of assumptions and variables (expected global growth, expected qty sold, development time, ...), and a few calculations "aside" (eg. development cost based on hourly wages and development time), and need to add the latter to the "fixed" data and mash it all up to get to the results.
The end result should be a few tables and graphs showing various scenarios (based on different values for the assumptions and variables), with a few sliders/input boxes/... where I can manipulate in real time the variables.
I have always used "basic" Excel functionality (tables and formulas) but I am now getting into Power Query and Power Pivot. I like the latter a lot, but am wondering how suited they are for my problem? Aren't they more for visualizing existing data, and less for performing calculations, extrapolations and projections toward the future on them?
Thanks for any advice, pointers or links you can give!
Nov 10 2020 01:15 PM
IMHO, you need mix of all.
Power Query is ETL (extract, transform, load) tool to pickup data from different sources, transform it and load to data model or/and directly into Excel Table.
Power Pivot is the interface to work with data model close to tabular model Sequel Server Analysis Services, you may use both DAX and MDX.
However, if you never used them initial time investment (and on later steps as well) to start working with them will be more significant than with formulas.
But that all depends on complexity of data and desired solution, I don't mean the size.
Nov 11 2020 12:33 PM
@Sergei BaklanThank you! You're probably right. I think I'll go with Power Query to extract and clean the necessary data from the original SQL export, and then continue with standard formulas (not Power Pivot).
Nov 11 2020 12:50 PM
I'm not sure how familiar you with Power Query, just take into account you may use embedded SQL query, but with this you lost query folding. What's better to use depends on concrete situation.