Match values from a query table to a table with fixed rows and columns

Hello fellow forum members,

i have a question regarding how can i put values from a table that is changing based on a power query in to a new fixed "pivot table" . for example:

i have this data

NAME COUNTRY AGE

ana brasil 10

peter france 20

grace spain 30

and i have a new spreadsheet with fixed rows with NAME and columns with COUNTRY , i just want to put the AGE in the correct order like this :

------brazil----france--spain

ana --10--------------------

peter ----------20------------

grace -------------------30---


Remember that the origin table will change the all the data but i have fixed the position of the NAMEs and LOCATIONs in the second table .

Hope you understand my problem.
thank you!

Best Regards

You could use CUBEVALUE functions.

Load your Power Query data to the data model, and create an explicit Measure for Age - something like:

Please Login or Register  to view this content.

Now you can layout your fixed table, and use CUBEVALUE formulae, like:

Formula:

Please Login or Register  to view this content.

See attached workbook for worked example.

Hey Olly !
you absolutely nailed my problem, thank you for that!
However im strugling with the step "create an explicit Measure for Age" i see that option in pivot menu table but i think its not there that i should add the code.
Could you elaborate more ?
thanks again!

Click Power Pivot > Data Model > Manage. This takes you to the Power Pivot window.

Now in the grid at the bottom of each table, you can create MEASURES.

In the file I uploaded, you'll see the measure [Age Measure] in the grid at the bottom of Table1. Click on that cell of the grid, and the DAX code for the measure is displayed in the formula bar (you can expand the formula bar down to display more).

Alternatively, you can click Power Pivot > Calculations > Measures > Manage Measures, then you can add / change measures via a series of dialogs.