Rearrange a table in Power query

Question

I have a table in Power Query that I'm trying to rearrange. The original table looks like this:

I want to rearrange it to look like:

I would appreciate any help in solving this issue. Thanks in advance!

Sara

I have noticed that if I want my desired result it would be looks like below in power query:

Answer 1

Given this as a starting table:

product	flow	indicators\TIME_PERIOD	2020
71	1	QUANTITY_IN_100KG	5630860
71	1	VALUE_IN_EUROS	35176720
71	2	QUANTITY_IN_100KG	8328942
71	2	VALUE_IN_EUROS	52490848

In PowerQuery, you will need to add a new Custom Column for "time" taking the last column name for the value (eg 2020). Then Pivot Column on indicators\TIME_PERIOD and 2020 (ie the previous last column).

This will give you a table like so:

product	flow	time	QUANTITY_IN_100KG	VALUE_IN_EUROS
71	1	2020	5630860	35176720
71	2	2020	8328942	52490848

Here's the full PowerQuery of the above:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"product", Int64.Type}, {"flow", Int64.Type}, {"indicators\TIME_PERIOD", type text}, {"2020", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "time", each List.Last(Table.ColumnNames(#"Changed Type")), type text),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"indicators\TIME_PERIOD"]), "indicators\TIME_PERIOD", List.Last(Table.ColumnNames(#"Changed Type")))
in
    #"Pivoted Column"

I would recommend leaving the structure like this, as you can then add (append) other similar tables together, to give you the one table of data.

Regarding the presentation, again I would recommend tackling this in Excel and not PowerQuery. Keep your PowerQuery table as above, then once loaded in Excel, your can add a reference to the product column and then hide that column.