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 yea...
Need Help
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

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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