Data Model assistance

Power Query Data Setup
1

Good day

need some guidance on data modelling, i have 6 fact tables from different sources and i would like 1 lookup table with 2 values:

  1. Region
  2. Country

all fact tables have Region and Country present, i have managed to make 1 relationship between region but make the country relationship

image
image1441×719 43.9 KB

getting the below error, any advice on how to get around this?

image
image669×651 18.6 KB

2

Hi @neilonbooysen,

I see a couple of challenges here in the screenshots.

  • you should have a single key column in a dimension/lookup table
  • you have nulls in your dimension/lookup table
  • you have bi directional relationships between your fact- and dimension/lookup tables

The solution is to generate a unique key for each Region/Country combination in the Dimension table maybe by concatenating those values in a new column.
Next make sure your Key column doesn’t include nulls and has a value for each possible combination present in your fact tables.

Combine all Region/Country fields in your fact tables as well, to generate the same Key and link the tables on this column in a one-to-many, single direction relationship from Dimension to Fact.

I hope this is helpful

1 Like
3

thanks Melissa for the insights, this definitely guided me