Hello everybody!
I was able to read answers to my questions so many times on this forum before.
For the question on my mind now, I get confused quite a bit. Let me explain:
I work with software which exports many different types of result files in .xlsx format. The basic layout is the same for each of these files (about 5000 rows and 100 columns of plain data with a few header rows).
For each of these export files, I want to import them depending on some filename conditions, remove the redundant top rows, standardise some stuff etc. So far, this seems like a perfect job for PowerQuery.
Next step however, is to perform calculations such as dividing all datapoint in file A by those in file B.
In PQ I can do divide operations, but only by column and within the same query. So basically I want to let PQ divide everything from A by B (=query C). Same goes for other relatively simple operations.
Note: All the mathematical operations I'd like to perform between queries are similar: e.g. divide each cell of range B2:AA5000 by the corresponding cell in query B range B2:AA5000
I could just import the queries I need and do those calculations directly with traditional formulas in Excel, but that would still require a huge number of tabs for all intermediary results which is not an elegant solution.
My impression (with limited PQ knowledge) is that PQ is great for importing and prepping inputs. But for mathematical operations on multiple queries with each other, I get a bit lost.
Searching for answers almost always leads me to specific PowerBI solutions. In this case I just want to perform everything within Excel; to keep it simple for co-workers to follow and because the purpose is not to create any sort of dashboard.
Should I be looking at PowerPivot for a solution?
Thank you!
I was able to read answers to my questions so many times on this forum before.
For the question on my mind now, I get confused quite a bit. Let me explain:
I work with software which exports many different types of result files in .xlsx format. The basic layout is the same for each of these files (about 5000 rows and 100 columns of plain data with a few header rows).
For each of these export files, I want to import them depending on some filename conditions, remove the redundant top rows, standardise some stuff etc. So far, this seems like a perfect job for PowerQuery.
Next step however, is to perform calculations such as dividing all datapoint in file A by those in file B.
In PQ I can do divide operations, but only by column and within the same query. So basically I want to let PQ divide everything from A by B (=query C). Same goes for other relatively simple operations.
Note: All the mathematical operations I'd like to perform between queries are similar: e.g. divide each cell of range B2:AA5000 by the corresponding cell in query B range B2:AA5000
I could just import the queries I need and do those calculations directly with traditional formulas in Excel, but that would still require a huge number of tabs for all intermediary results which is not an elegant solution.
My impression (with limited PQ knowledge) is that PQ is great for importing and prepping inputs. But for mathematical operations on multiple queries with each other, I get a bit lost.
Searching for answers almost always leads me to specific PowerBI solutions. In this case I just want to perform everything within Excel; to keep it simple for co-workers to follow and because the purpose is not to create any sort of dashboard.
Should I be looking at PowerPivot for a solution?
Thank you!
