Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
Anonymous
Not applicable

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 me the missing days. The start and end dates in the desired series should be the oldest and the latest date in the existing column consecutively, how could I do that?

Image #1

 

I applied this code that got me a part of the solution but created for me another problem

 

let

Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Schedule", type text}}),

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"}),

#"Filled Down" = Table.FillDown(Reordered,{"Name"}),

#"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,"Weekend",Replacer.ReplaceValue,{"Schedule"}),

#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Name", Order.Ascending}, {"Date", Order.Ascending}})

in

#"Sorted Rows"

 

And this got me the below output:

Image #2

 

Now, problem is happening when there is more than 1 name in the data source table like the below:

Here we get undesirable output and the transformation is applied only in the latest name in the list but not on the rest of the names

Image #3

 

Magdoulin_0-1630159311362.png

 

Magdoulin_1-1630159342297.png

 

Magdoulin_2-1630159374013.png

 

1 ACCEPTED SOLUTION

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"

 

 

View solution in original post

4 REPLIES 4
Jakinta
Solution Sage
Solution Sage

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

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
Not applicable

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

 

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

 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors