Transform Data from Report into Table: what should I use Power Query, Macro?

[arnoldip]

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!

[#2]

@arnoldip 

You can apply Power Query. VBA would be a simple solution as well.

[#3]

Thanks! I'm opened the file and I'm trying to understand the steps you applied.

Could you further explain?

Thanks!

[#4]

@arnoldip 

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