Forum Discussion
Transform Data from Report into Table: what should I use Power Query, Macro?
Good afternoon all!
This is my first post in this forum, thanks in advance for the help!
I use a system where I can extract the following report with the following information, let's call it REPORT 1. Information looks like this:
| TYPE OF PRODUCT | PRODUCT A | PRODUCT A | PRODUCT A | PRODUCT B | PRODUCT B | PRODUCT B |
| City | Delivered | Sales order | Stock | Delivered | Sales order | Stock |
| City 1 | 728 | -585 | 125 | 728 | -592 | 119 |
| City 2 | 1.076 | -1.168 | 60 | 1.412 | -1.17 | 62 |
However, I want to format it like a dataset for a table, like this (let's call it REPORT 2)
| City | Type of Product | Status | Qty |
| City 1 | PRODUCT A | Delivered | 728 |
| City 1 | PRODUCT A | Sales order | -585 |
| City 1 | PRODUCT A | Stock | 125 |
| City 1 | PRODUCT B | Delivered | 728 |
| City 1 | PRODUCT B | Sales order | -592 |
| City 1 | PRODUCT B | Stock | 119 |
| City 2 | PRODUCT A | Delivered | 1.076 |
| City 2 | PRODUCT A | Sales order | -1.168 |
| City 2 | PRODUCT A | Stock | 60 |
| City 2 | PRODUCT B | Delivered | 1.412 |
| City 2 | PRODUCT B | Sales order | -1.17 |
| City 2 | PRODUCT B | Stock | 62 |
How should I approach this taks considering this is information I'll download once a week, so I want to set it up in a way I only need to refresh information from REPORT 1 and see it as in the REPORT 2.
Thanks!
3 Replies
- Thanks! I'm opened the file and I'm trying to understand the steps you applied.
Could you further explain?
Thanks!After opening the Power Query Editor you can view all the applied steps of the query. You can click on any step to view the updated data.
Some steps have gear icons. You can click on this icon to view further information on the applied step.
If you are interested in Power Query you could start with this introduction.
Introduction to Power Query - Excel Off The Grid
