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
    5 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
5 years ago

You can paste this in Blank Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MT8kvzcnMU9JRstA30jcyMDIEMoMzMtNKlGJ10BUYE1JgQkiBKSEFZoQUWBJSYGhAUIUhQRUEg8KQYFgYonslFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Schedule = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Dates = Table.FromColumns({List.Transform({Number.From( (Date.From(List.Min(#"Changed Type"[Date]))))..Number.From( (Date.From(List.Max(#"Changed Type"[Date]))))}, Date.From)}, {"Date"}),
    Merged = Table.NestedJoin(Dates, {"Date"}, #"Changed Type", {"Date"}, "Gr", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Gr", {"Name", "Schedule"}, {"Name", "Schedule"}),
    Reordered = Table.ReorderColumns(Expanded,{"Name", "Date", "Schedule"})
in
    Reordered