Forum Discussion

brux2dc's avatar
brux2dc
New Member
6 years ago

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

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.

Need Help

6 Replies

  • brux2dc's avatar
    brux2dc
    New Member
    6 years ago

    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's avatar
      Mariusz
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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's avatar
        brux2dc
        New Member
        6 years ago

        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