M Code generating Week numbers

[tonijj]

Hi,

Im building a report to show Resource Planning at our company. As such Im trying to use M Code to populate a list of data based on the datatable. 

What I want to achieve;

M Code to generate 1 row per week number - based on the set in the datatable. 

Example: In the picture below, as I have a Start Date (Week number) and End Date (Week Number), I want the M code to generate 1 row of data per week, within that range. 

So, if the Start Date is Week 5, and End Week is 10 - it should therefore generate 5 rows. 

1 row for week 5

1 row for week 6

and so on....

I have done similar in the past (with help from this excellent community!) to generate if you set a start and end date using a DateKey, such as "2020-01-01" to "2020-12-31". So for days it works great, but using it with Week numbers instead was a whole different story for me 😞 

That code was something like this (for reference): 
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Date", each List.Dates([Start],Duration.Days([End]-[Start])+1,#duration(1,0,0,0))),
#"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Date",{"Start", "End"}),

End Result;
I want to be able to visually show the resource planning and utilization, based on weeks, like something like the picture below. 

Bonus Question: 
How could I make sure it works for e.g 2 year, meaning, if the assignment spans from 2020 to 2021?

Example data shared here:

Reference material 

[ImkeF]

Hi @tonijj 

please check out this function: https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-... 

[tonijj]

Hi Imke,

Thank you for taking time!

I've read your full article (great one by the way!). Unfortunately I dont think it takes me all the way to the finish line. That could be on the other hand, due to my lack of knowledge of coding 😕 

The result I want to achieve is that the code generates X amount of automatic rows depending on the span. So for the example below, its a range of 5 weeks (10 to 15) and hence generating 5 rows as such. 

When adding your code this is what I get, and again, it might just be my lack of knowledge here!

[ImkeF]

Hi @tonijj  

Thank you! I think you're nearly there. What you've achieved is a list of dates with weekly intervals. Just transform those with a function like Date.WeekOfYear for example and you'll get the week numbers.

[tonijj]

@ImkeF 

Any chance you could take a quick look the Example file I made available via OneDrive with my example data? I tried adding date.weekofdays but with no luck...

Would really appreciate if you have 2min just to open my workbook, and maybe add your magic 🙂  

[Greg_Deckler]

@ImkeF