Lookup in interval(s), not unique

[Anonymous]

Given below three tables, in my FACT table, for each row I have a Postal Code. I need to identify, from the Zone table, which Zone ID this Postal Code lies within, and then lookup the price unit of that zone ID in the price table.

Obstacles:

- The zone table is in interval form. Would the best approach be, to explode the intervals, generating a new table with all postal codes and their corresponding Zone ID? How is this done?

- The Zone ID is not unique, meaning there can be multiple intervals per zone. How to establish the lookup key(relationship)?

Ultimate goal: Ability to map the price of a Postcal code given the tables below.

[tex628]

You can use the 

List.Numbers([PostCode from],[Postcode to] - [Postcode from] +1 )

To create new records for each postcode. 

Regarding linking the facts table to the zone table i need to know more. If i understand things correctly you will have duplicate postcodes in both the zone table and the facts table.

What exactly is the data in the facts table?
Is there only a single postcode?

[Anonymous]

My assumption is, that you are stating that exploding the intervals is the best approach.

List.Numbers, is that a Power Query function? Could you please elaborate on how to apply it? I will need to more columns in the new table from the Zone Table.

I do not have duplicate post codes in the Zone table. I have intervals, not overlapping so to speak. I have duplicate zones however, one for each interval.

In the FACT table, there are Many postal codes. Say, many orders from various and same postal codes.

[tex628]

List.Numbers() is a powerquery function. It takes 2 parameters, a starting number and a counter.

Since i cant see your facts table it's dificult to determine the best approach. I dont know if im supposed to apply the price to each row in the facts table or if I need to aggregate it somehow before that. 

Could you possibly give me some insight into the structure of the fact table?
And what the desired outcome looks like visually? 

[Anonymous]

Say each row is an order, with destination postal code. I need to determine the price of each order, which is dependent on the postal code in the way described in the post.

I then need to accumulate cost across various dimensions, time periods, etc.

[tex628 · Accepted solution]

If that is the case, list.Numbers() will definatly be the best option.
Add it as a custom column in the query editor and then expand it to new rows.
This should give you a row for each postcode that you then can use to create a relationship with the facts table.

[Anonymous]

---

@tex628 wrote:

If that is the case, list.Numbers() will definatly be the best option.
Add it as a custom column in the query editor and then expand it to new rows.
This should give you a row for each postcode that you then can use to create a relationship with the facts table.

---

Fantastic, solved my problem. I hope this is the most efficient solution, but never the less marking as answered.