Repeat Rate by Period Range Text

[JustDavid]

Hello,

I'm trying to do a equivalent of "vlookup" that has the "closest match" in Power Query. But given the data that I had, am trying to know how I can create date from string range (i.e. Feb 23 - Mar 1)

As screenshot shows below, my PQ data is shown below and my desired result is shown in Excel range column K to M

Because it's file name and there's many variations, what would be the best way to get the "start" and "end" date and then "fill in the gaps" of the date, which then finally assign the rate based on the period.

I have attached the Excel File https://docs.google.com/spreadsheets/d/17foxYBLODLDD75nsUygweLCSyjL9UwBf/edit?usp=sharing&ouid=10270... 

Thank you in advanced

[JustDavid · Accepted solution]

I was able to play and google around and was able to resolve it.

What I did is create a 2 columns to get the 'start date' and 'end date' based on the text, and these 2 columns are date type.

After which from (https://www.youtube.com/shorts/V6lEUNvbXug), I was able to create the list of dates from these 2 date columns

[v-abhinavmu]

Hi @JustDavid,

Thank you for the update, I'm glad to hear you were able to resolve it. Thanks for sharing the approach you used as well, it will be helpful for other community members who come across a similar scenario.