Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
I have the following data. I would like to create a list of dates between start and end dates. I have tried using a List but because I have the days of the week (attribute) listed as well as the corresponding time worked (value) , I get a list dates between the two dates, 7 times. What I need to see is the date against each week day that is between the start and end dates. I suppose basically something like a fill down or formula that adds 1 day increment to each row and then on the 8th row, it starts from that date and adds 1 day increments again.
Can anyone help me out please?
Solved! Go to Solution.
Hi @KazL,
Please try this ...
Just add the Ordinal value of [Attribute] to the [Start of Week]
eg. Date.AddDays([Start of Week], List.PositionOf({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},[Attribute]))
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PU9JRMtI3sNA3MjAyBLKBLBgnVidaKaQ0Fb+C8NQUAiZklOJX4FaUiV9BcGIJAQWlBHwB86ahIUjMAKIAxkH2Jk4FMG/iNgHqTZwKYN7EqQDmTdwKSvH5IhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, #"Start of Week" = _t, #"Week Ending" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Week", type date}, {"Week Ending", type date}}),
#"Added Week Day Number" = Table.AddColumn(#"Changed Type", "Week Day Number", each List.PositionOf({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},[Attribute]), Int64.Type),
#"Added Date" = Table.AddColumn(#"Added Week Day Number", "Date", each Date.AddDays([Start of Week],[Week Day Number]), type date)
in
#"Added Date"
Hi @KazL,
Please try this ...
Just add the Ordinal value of [Attribute] to the [Start of Week]
eg. Date.AddDays([Start of Week], List.PositionOf({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},[Attribute]))
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PU9JRMtI3sNA3MjAyBLKBLBgnVidaKaQ0Fb+C8NQUAiZklOJX4FaUiV9BcGIJAQWlBHwB86ahIUjMAKIAxkH2Jk4FMG/iNgHqTZwKYN7EqQDmTdwKSvH5IhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, #"Start of Week" = _t, #"Week Ending" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Week", type date}, {"Week Ending", type date}}),
#"Added Week Day Number" = Table.AddColumn(#"Changed Type", "Week Day Number", each List.PositionOf({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},[Attribute]), Int64.Type),
#"Added Date" = Table.AddColumn(#"Added Week Day Number", "Date", each Date.AddDays([Start of Week],[Week Day Number]), type date)
in
#"Added Date"
Thanks pbi101. That worked well. Was just what I needed.
I appreciate your time in helping me out.
Cheers
Hi @Anonymous ,
You can follow below article. It helped me previously for same issue:-
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Thanks Samarth. I have tried this but it didn't work as I get 7 lots of 7 days in the new column. This is because I have 7 rows of the same start and end date.
Karen
@Anonymous , Something like this , a new column in M
List.Dates([Start Date], Duration.Days([End Date] - [Start Date]), #duration(1,0,0,0))
or
List.Dates([Start Date], Duration.Days([End Date] - [Start Date])+1, #duration(1,0,0,0))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |