Generate dynamic rolling 365 day Calendar with Names pulled from another table

[brux2dc]

I'm new with Power Bi and Power Query.

I'm trying to generate a dynamic calendar table which has a Start and End date column one day apart for every person in a names table over the span of a year.

To clarify, I have a table with Employee names and need to generate a seperate table with daily date spans for each person in the names table.

Start     End       Name

1/1/19  1/2/19  Bob

1/2/19  1/3/19  Bob

1/3/19  1/4/19  Bob

....

1/1/19  1/2/19  Sarah

1/2/19  1/3/19  Sarah

1/3/19  1/4/19  Sarah

I've figured out how to generate the dates for a year, but can't seem to perform a loop where each person from the employee names table gets attached to each generated date pair in it's own name column.

I'm aware this will generate a table with x*365 rows.  In my case ~200*365.

Thanks.

[brux2dc]

Wow that was fast!

My version of Power BI Desktop is older than the attached file, so won't open it.  I can't update the version of Power BI, and my organization won't either for many months.  Policy is basicly to be a few versions behind.  

Could you post the code here??

Thanks!

[Mariusz]

Hi @brux2dc 

Please see the below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLJR0wxwjGccpPUorViUYVBHGMsakwRlZhgk0FdluCE4sSMwjYg6oGu01QNbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}, {"Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each  { Number.From( [Start] ) .. Number.From( [End] ) } ),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type2"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[brux2dc]

Thanks, but this isn't what I need. The names are in a diffrent table. I need to take each name and attach the dates to them in a seperate table.   This shouldn't happen in the Names table.

Starting with:

Start    End

1/1/1  1/2/1

1/2/1  1/3/1

and ending with:

Start   end     Name

1/1/1  1/2/1   Bob

1/2/1  1/3/1   Bob

So:

In New Table "Filled"

(

     For each name in Table.Names

           (

                Generate 365 date span rows

           )

)

Output being rows of:

Start Date - End Date - Name

1 row per date span per unique name from the seperate names table.

If this was a normal program I'd store the names in an array and then iterate over the array to generate a new array with the dates and names.

Thanks

[v-shex-msft]

HI @brux2dc ,

Can you please share some dummy data with the same data structure with the expected result for test?

How to Get Your Question Answered Quickly 

In addition, I think you can also try to use Dax calculated tables to achieve your requirements.

Regards,

Xiaoxin Sheng

[Mariusz]

Hi @brux2dc 

You can add a Custom Column to your Names table and type in the formula are the name of the dates table like below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Mariusz]

Hi @brux2dc 

Please see the attache file with the solution 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.