how to pivot a table in-memory? using summarize+addcolumns?

Thank you for the quick reply! I forgot to mention, that I do not wish to physically create a new table. I wish to use this in-memory table as an interim step in a measure calculation.

Hi Charlie

Thanks. Correct, but in-memory, since I do not want to add any more phyisical table objects to my data model.

Do you mind sharing that specific pbix file of yours? 

Cheers, chefe

++++++++++++ edit ++++++++++++

Now I got it, thank you. Don't need to share the file now :)

I was totally unaware of the "calculate table" functionality :-O making tables using DAX syntax that otherwise could only be seen using something like DAX Studio (which I cannot install on my machine unfortunately...), so it is the same coding to use in a measure... brilliant. Thanks again ;)

Though I did have to alter your code a bit:

* using =addcolumns() in the formula instead of - I guess - adding calculated columns using the UI

The syntax - which now should be usable as an interim step in any measure (?) - is now as follows:

MyNewTable = 
ADDCOLUMNS(SUMMARIZE(Table2;Table2[id])
	;"fx leg amount"
	;LOOKUPVALUE(Table2[amount]
		;Table2[leg #]
		;"leg1"
		;Table2[id]
		;[id]
	)
	;"fx leg ccy"
	;LOOKUPVALUE(Table2[unit]
		;Table2[leg #]
		;"leg1"
		;Table2[id]
		;[id]
	)
	;"base leg amount"
	;LOOKUPVALUE(Table2[amount]
		;Table2[leg #]
		;"leg2"
		;Table2[id]
		;[id]
	)
	;"base leg ccy"
	;LOOKUPVALUE(Table2[unit]
		;Table2[leg #]
		;"leg2"
		;Table2[id]
		;[id]
	)
)

Problem solved. Thanks again.