Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply

Populate dates between SoW and EoW

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:

ShelleyBaynton_1-1675872285174.png 

 

ShelleyBaynton_0-1675872073400.png

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 

1 ACCEPTED SOLUTION
AnastasiaS
Resolver I
Resolver I

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

AnastasiaS_0-1675876379800.png

 

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"

AnastasiaS_2-1675876488805.png

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

AnastasiaS_3-1675876653924.png

 

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,

View solution in original post

1 REPLY 1
AnastasiaS
Resolver I
Resolver I

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

AnastasiaS_0-1675876379800.png

 

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"

AnastasiaS_2-1675876488805.png

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

AnastasiaS_3-1675876653924.png

 

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,

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Carousel June 2024

Check out the June 2024 Power BI update to learn about new features.

2
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors