Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Adding Missing Dates to a Table

  Hi guys, I need your help please. As you could see the below table screen shot got a column that has a list of dates, but it misses some days in the middle. I hope to build a query that add for ...
Need Help
  • Jakinta's avatar
    Jakinta
    4 years ago

    This should do your thing.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MT8kvzcnMU9JRstA30jcyMDIEMoMzMtNKlGJ10BUYE1JgQkiBKSEFZoQUWBJSYGhAUIUhQRUEg8KQYFgYYvFKREVFBVDAHFtQw+WwmAyXwxLAcDkMj8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Schedule = _t]),
    DateType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Grouped = Table.Group(DateType, {"Name"}, {{"Date", each List.Transform({Number.From( (Date.From(List.Min(_[Date]))))..Number.From( (Date.From(List.Max(_[Date]))))}, Date.From) }}),
    #"Expanded Gr" = Table.ExpandListColumn(Grouped, "Date"),
    #"ChangedType" = Table.TransformColumnTypes(#"Expanded Gr",{{"Date", type date}}),
    #"FINAL" = Table.AddColumn(ChangedType, "Schedule", each if Date.DayOfWeek([Date],Day.Monday) >4 then "Weekend" else "Shift")// or do a merge with Source table if you have some other condition
in
    #"FINAL"

     

     

Jakinta's avatar
Jakinta
Solution Sage
4 years ago

This should do your thing.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MT8kvzcnMU9JRstA30jcyMDIEMoMzMtNKlGJ10BUYE1JgQkiBKSEFZoQUWBJSYGhAUIUhQRUEg8KQYFgYYvFKREVFBVDAHFtQw+WwmAyXwxLAcDkMj8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Schedule = _t]),
    DateType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Grouped = Table.Group(DateType, {"Name"}, {{"Date", each List.Transform({Number.From( (Date.From(List.Min(_[Date]))))..Number.From( (Date.From(List.Max(_[Date]))))}, Date.From) }}),
    #"Expanded Gr" = Table.ExpandListColumn(Grouped, "Date"),
    #"ChangedType" = Table.TransformColumnTypes(#"Expanded Gr",{{"Date", type date}}),
    #"FINAL" = Table.AddColumn(ChangedType, "Schedule", each if Date.DayOfWeek([Date],Day.Monday) >4 then "Weekend" else "Shift")// or do a merge with Source table if you have some other condition
in
    #"FINAL"

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

You're genius, thanks a million, you saved my day.