Populate dates between SoW and EoW

[Shelley-Baynton]

Hello,

I have a standard fiscal calender which I liked to populate with our companies bespoke FY details which are held within another Power Query:

 

I've tried the following:-

• Created a relationship between SoW and Week Commencing
• Used Related function to pull through
• Merge the power queries to create new data set

Neither options returned a workable solution 😥

Any assistance would be warmly welcomed 

[AnastasiaS · Accepted solution]

Hi  @Shelley-Baynton 

You could try to add to your table containing SoW and EoW, a column that will contain the dates between the start and end : 

In power query

1. go to add column -> Custom column

Type the following code:

={Number.From([Start])..Number.From([End])}

where you replace [start] and [end] with the right col names

This will add you a new column. Then click on the icon on the right and select "Expand to new rows"

This will add you a row per date; you'll only have to transform the type to date as it will be numeric:

Then, you could merge your date table with this table, based on the newly created column. You'll have matching values as both contain daily rows.

After merge, you can chose to keep only the FY detail column.

Could you try that and tell if this corresponds to your need? Don't hesitate if smth is not clear.

Regards,