Can I create a lookup Table for Values between two dates in Powerquery?

[ChromeMystic]

I have solved this with DAX at the Moment but I would love to do it in PowerQuery, so I can create all my tables inside Dataflows.

I got a table with our KPI Values as well as the Start and end date.

Here is a Mockup on how it works.

StartDate	EndDate	KPIName	Green	Yellow	Red
01.01.2021	31.12.2021	KPIValue1	1	2	3
01.01.2021	31.12.2099	KKPIValue2	5	9	16
01.01.2022	31.12.2099	KPIValue1	1.5	2.5	3.5

In PowerBi Desktop I created a new Table that Pulls the Dates from my existing Datetable (although the Function on start and end Date I can easily Replicate in PowerQuery) and has Custom Rows that run the following DAX Code:

value1G = 
var __kpi = "kpiValue1" // set the KPI for this column
var __date = [Date] 
var __result = 
MAXX(
    FILTER(
        sedKPI,
            __date>=sedKPI[ValidFrom] && __date<=sedKPI[validTo] && sedKPI[KPI] = __kpi), // Filter if current date is between valid from and until
sedKPI[green] // select column that contains the value
)
return __result

This results in a lookuptable, that shows the current KPI Value for each day of my data. These I then use in my conditional Formatting and other functions for KPI breeches. As the KPI values change from time to time (normally once per year but we had changes that were done during a calendaryear as well) I don't just want to set a global value per KPI, as this would show the wrong results for older Data.

I found solutions on how to select data from a table based on one date with the Merge function in PowerQuery but I wasn't able to build something that checks wether todays date is between the start and enddate of my KPI Table and looks up the respective fields. Preferably I would do this with a custom function that gets the KPI value to look for as variable and then does the rest by itself.

[ChromeMystic]

Thank you!! This worked as I need it. 

[v-yanjiang-msft · Accepted solution]

Hi @ChromeMystic ,

According to your DAX formula, here's my PowerQuery solution.

Create a custom column.

= Table.SelectRows(sedKPI, 
    		(sedKPI) =>(sedKPI[ValidFrom] <= [Date]) and
       		(sedKPI[ValidTo]  >=  [Date]) and 
       		(sedKPI[KPI] = "KPIValue1")
        	)[Green]{0}

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.