Unpivot with Powerquery and add column

[bullit_nl]

Hello,

I have a question about un-pivoting with Powerquery.

On my basis data sheet I made a table of the data. This data I unpivot with PowerQuery.
This generates a new sheet named PowerQuery. On this sheet I now have the Unpivot data.

I want to use the data (unpivot) from the sheet PowerQuery to make a Pivot Table.
For this Pivot Table I need some extra coloumns so I added these columns to the sheet PowerQuery created.
Then I mande my Pivot Table.

Now if I add data to my table on the basis data sheet and refresh the powerQuery sheet the extra columns are gone.

I just started with using PowerQuery so maybe I'm doing somthing the wrong way.
What would be the right way to do this?

Thanks

 

[FranzV]

*Note: The menu and option names are probably different since I am translating from Spanish.

You can normally solve extra column problems by checking the 'Keep sorting, filters and design' option in the 'External Data Table Properties' dialog. You can find this dialog by right clicking any cell in the table and choosing 'Table' => 'Query Properties'.

If you manually enter data into the table, I would recommend Matt Allington's Self Referencing Tables approach.

I hope it helps.