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

New Contributor

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 PRODUCTPRODUCT APRODUCT APRODUCT APRODUCT B
PRODUCT BPRODUCT B
CityDeliveredSales orderStockDeliveredSales orderStock
City 1728-585125728-592119
City 21.076-1.168601.412-1.1762

 

However, I want to format it like a dataset for a table, like this (let's call it REPORT 2)

CityType of ProductStatusQty
City 1PRODUCT ADelivered728
City 1PRODUCT ASales order-585
City 1PRODUCT AStock125
City 1PRODUCT BDelivered728
City 1PRODUCT BSales order-592
City 1PRODUCT BStock119
City 2PRODUCT ADelivered1.076
City 2PRODUCT ASales order-1.168
City 2PRODUCT AStock60
City 2PRODUCT BDelivered1.412
City 2PRODUCT BSales order-1.17
City 2PRODUCT BStock62

 

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

@arnoldip 

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

city type of product.JPG

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

Could you further explain?

Thanks!

@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.

applied steps.JPG

Some steps have gear icons. You can click on this icon to view further information on the applied step.

gear icon.JPG

If you are interested in Power Query you could start with this introduction. 

Introduction to Power Query - Excel Off The Grid