Conditional Merge

[LINUS_MAASTOK]

Good evening everyone, I had a question ... btw, excuse my bad english:

I have a couple of tables, CONTRIBUTION and BILLING, which have an "ID" field with which I relate them (the relationship between the tables is * (CONTRIBUTION) -> 1 (BILLING)), as well as a CONSUMPTION (numeric) field , a CONSUMPTION_PERIOD (numeric) field and a DATE (date) field with which it is possible to see what contribution was successfully billed.

To see what contribution was billed i make a Merge Table (Inner) between both tables, relating identical ID, CONSUMPTION and CONSUMPTION_PERIOD, which gave me a momentary valid Merge table.

Now I have been informed that there are contributions that can be billed with a margin of + - 5 in the CONSUMPTION field, that is, they should be able to join with an identical ID, an identical CONSUMPTION_PERIOD, variable DATE and CONSUMPTION with a margin of + -5 of variation

My question is, how can I make a table (like te merge table) that relates the CONSUMPTION in a specific range?

Thanks in advance <3 <3 <3

 

[macfuller]

First, what is the +/- variation calculated upon if there are multiple values? Min, Max, Average, fixed?

Assuming you're doing this in Power Query, try:

1. Remove the Consumption field from the merge so that all consumption values for the specific ID and Period are joined
2. Create a temporary column with the absolute variance (absolute or percentage) based on whatever the formula is.
3. Filter only the rows that are within the +/- variance
4. Delete the temporary column