Link two tables with a range in stead of one value

[Anonymous]

Hi,

I have this table SALES containing postal codes from Belgium

I need to make aggregation totals per province and per region.

To do so I have this POSTAL CODES table from which must be decides in which province and in which region a postal code resides.

e.g. Postal code 4715 lays between 4000 and 4999 and so it's in the province of Liège and in the Region of Wallonie

I need to link the Sales table to Postal Codes table where 'Sales'[Postal Code] lays between 'Postal Codes'[Postal Code From] and 'Postal Codes'[Postal Code To]

How do I do this?

R.W.

[LivioLanzo]

Hello @Anonymous

you need to make changes in your dimension table (postal codes) during the ETL process and repeat each row by the number of postal codes between START postdal code and END postal code, adding a column containing the sequencial postal code

which becomes the primary key that you shall use in the relationship

[Anonymous]

Hi @LivioLanzo

Thanks for your reply.

How do I do this (repeat each row by the number of postal codes between Postal Code From and Postal Code To)?

Thanks,

R.W.

[LivioLanzo · Accepted solution]

With Power Query it is easy:

let

    // load of your table from your database, excel, csv, etc
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MDAAUUaWlkDKqai0uDg1pxjE9HBXitWJVjICyRpDFJmAFYUn5uTk58EZmalgdcYgBaYQdZZgdWE5iYm5xVBGXkpqUWoeWKUJUMgIYq0RRKVjXkl5alFBah6mYlOgkDFEsbEJbmNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Postal Code ID" = _t, #"Postal Code From" = _t, #"Postal Code To" = _t, Province = _t, Region = _t]),
    
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Postal Code ID", Int64.Type}, {"Postal Code From", Int64.Type}, {"Postal Code To", Int64.Type}, {"Province", type text}, {"Region", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PostalCodePrimaryKey", each List.Numbers( [Postal Code From], [Postal Code To] - [Postal Code From] + 1 ), type list ),
    #"Expanded PostalCodePrimaryKey" = Table.ExpandListColumn(#"Added Custom", "PostalCodePrimaryKey"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded PostalCodePrimaryKey",{{"PostalCodePrimaryKey", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Postal Code ID", "Province", "Region", "PostalCodePrimaryKey"})
in
    #"Removed Other Columns"