Forum Discussion
Solved
All Dates between a Start and End Date
Hi, I have a table shown below. I want this to show number of workers needed for PSP PIPE (which is 10) on each week between 08-Mar-18 to 21-5-18. SLUHN Anderson PIPE would add 15 workers from 14-Ma...
Usman,
Please add a blank query in Power BI Desktop, paste the following code into Advanced Editor of the blank query to test the process.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3tNA3MjC0UNIBso0MYeyA4ACFAM8AVyDT0EApVges0gSh0tgAxg72CfXwU3DMS0ktKs7Pg2syhWoygis004fb5JtflFiWmZin4JGamFOSoZCYl6IQnJyZmpecqhCckZpaAjLBCGyCib6hMUybub4pjOmYm1gFtM01JRPJUgOYDkuEDoSjcWmJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Job = _t, #"Workers Needed" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Job", type text}, {"Workers Needed", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Start Date],Duration.Days(Duration.From([End Date]-[Start Date]))+1,#duration(1,0,0,0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Date"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Week", each Date.WeekOfYear([Date])) in #"Added Custom1"
Regards,
Lydia
Usman,
Please add a blank query in Power BI Desktop, paste the following code into Advanced Editor of the blank query to test the process.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3tNA3MjC0UNIBso0MYeyA4ACFAM8AVyDT0EApVges0gSh0tgAxg72CfXwU3DMS0ktKs7Pg2syhWoygis004fb5JtflFiWmZin4JGamFOSoZCYl6IQnJyZmpecqhCckZpaAjLBCGyCib6hMUybub4pjOmYm1gFtM01JRPJUgOYDkuEDoSjcWmJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Job = _t, #"Workers Needed" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Job", type text}, {"Workers Needed", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Start Date],Duration.Days(Duration.From([End Date]-[Start Date]))+1,#duration(1,0,0,0))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Date"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Week", each Date.WeekOfYear([Date]))
in
#"Added Custom1"
Regards,
Lydia