Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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!
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"
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
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.
Hi @brux2dc
Please see the attache file with the solution
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |