Power Query and assigning to ranges (merging of tables like VLOOKUP with last argument 1/TRUE))

This article describes, how to use Power Query to connect two tables using range or date interval (like VLOOKUP works if the last argument is TRUE).

For example here. In the left table, there are amounts of orders. The higher amount, the bigger discount – the list of discounts is in the middle table. 

The result is in the right table – containing total amounts and relevant discounts.

For example if the amount is 3 820, it is enough to 1% discount. 

The procedure is, I have to say, quite complicated – but I don´t know anything better…

At first, we will merge these tables using the Full outer connection type.

After expanding the tables, using the double-sided arrow, the tables are, more or less, side by side.

Now let´s merge the amounts from both tables into one column – using (for example) the conditional column.

Now use this column to sort the table. This mixes up the merged tables.

Now use the column with percentages (values to assign) to fill it down. Now it is assigned.

All that remains is to remove rows having null in “Total amount”, sort and remove unnecessary column.

That´s all.

This procedure will work the same way for dates and texts