Forum Discussion
Generate dynamic rolling 365 day Calendar with Names pulled from another table
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!
Community ChampionHi 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"
Mariusz
If this post helps, then please consider Accepting it as the solution.
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
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