Create Dimension table from multiple tables

[Anonymous]

Hi,

How do I create a dimension table from multiple (i.e. different) tables? And is DAX or Power Query more preferable? Or both?

(Please don't ask me to give a data example, because the question is quite clear). 

Regards,

[v-lid-msft]

Hi @Anonymous ,

If the two tables have one same column but have different value such as ID, we can create a calculated table:

IDTable =
UNION (
    DISTINCT ( 'Table A'[ID] ),
    DISTINCT ( 'Table B'[ID] )
)

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I've been making good use of the CROSSJOIN DAX function when creating a new table from existing tables.

It can let you turn two tables such as:

FoodType

Fruit

Vegetable

CookType

Baked

Fried

Into:

FoodType	CookType
Fruit	Baked
Vegetable	Baked
Fruit	Fried
Vegetable	Fried

If I'm understanding your question, this is what you're looking for?

This can be especially useful with the CALENDAR function / GROUPBY functions if you're combining several tables / want to add a date dimension from thin air. Be careful though - if you have too many dimensions, your record count can grow very quickly.

[PaulDBrown]

@Anonymous 

I take it the reason you need to create a dimension table from different tables is that not one of the tables contains the complete set of values you need as your lookup table, right? (If not, then just in Power Query create a new query referencing the table containing the whole range of values and then create the relevant relationships to the fact tables)

I would do it in Power Query using the append function to add all the necessary tables, removing unwanted columns, and removing duplicates from the main field etc...

[Anonymous]

What if  there is no table having the whole range of values??