PowerQuery - Modify data

[GeorgeTimes]

Hi all,

I have a raw data that stores invoices per month. Next month will add 2 more columns for invoice for that specific month (see below example of my raw data).

Column D and E - invoice for June 2021

Column F and G - invoice for July 2021

ONLY USE TO UPDATE PBI.xlsx
	A	B	C	D	E	F	G
1	Country	NovationDate	Region	PI Value Jun-21	App Value Jun-21	PI Value Jul-21	App Value Jul-21
2	Albania	Out of	EM	1742505.94		1771672.23	1771672.23
3	Algeria	01/03/2021	EM		1742505.94	370.70	426.06
Sheet1

I need to transform this in powerQuery in order to have something like below.

Any idea how I can do that? I need to have the same column names, but the raw data for those months to be displayed on new rows, not new columns

ONLY USE TO UPDATE PBI.xlsx
	A	B	C	D	E	F
15	Country	NovationDate	Region	PI Value	App Value	date
16	Albania	Out of	EM	1742505.94		Jun-21
17	Algeria	01/03/2021	EM		1742505.94	Jun-21
18	Albania	Out of	EM	1771672.23	1771672.23	Jul-21
19	Algeria	01/03/2021	EM	370.70	426.06	Jul-21
Sheet1

Columns A B C will always stay the same.

Need this to be transformed in PowerQuery or by VBA, don't need it in PivotTable.

 

[RoryA]

Select the first three columns, then right-click one and choose Unpivot other columns. That should give:

Now split the Attribute column by the last space delimiter:

Then pivot on Attribute.1 using Value as the Value field:

and rename columns as desired.