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
Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

 

I needed just to correct the change type step to make the date as date, not datetime as this prevented the merging step to act properly and it’s worked so far.

 

Now, the problem is, the generated extra raws come as empty when it comes to the name as per the below screenshot:

 

Magdoulin_2-1630056385205.png