Hello PBI experts! I was hoping you could help me with a question about merging queries.
Here's my basic set-up. I'm building a data model in direct query mode. I have two tables: FACT and TRANSLATION. FACT has two columns which give the month of some event as a number--let's say "Order Month" and "Recieved Month". The table TRANSLATION has a column "Month Number" and a column "Month Name".
I want merge TRANSLATION into FACT twice: once joining "Order Month" and "Month Number", and once joining "Received Month" and "Month Number". This will allow me to add "Order Month Name" and "Received Month Name" columns to my FACT table.
Is it more efficient to load two copies of the TRANSLATION table, and merge each of those with FACT once; or to load the TRANSLATION table once and merge the same table into FACT twice? Or does this not matter?
P.S. In case this seems like a lot of work just to get month name columns--this approach does make sense for me, for a mix of technical and business reasons. The TRANSLATION table actually contains month names in multiple languages, but it's filtered to only have the rows for a given language at any one time.
