Adding Missing Dates to a Table

[Magdoulin]

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




Image #1




Image #2




Image #3

 

[Magdoulin]

This post is published originally here:

https://www.reddit.com/r/PowerQuery/comments/pc8w8o

 

[alansidman]

Crossposted at: Adding Missing Dates to a Table

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel Power Query: Adding Missing Dates to a Table

In future please supply links to ALL sites where you ask the same question.
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Magdoulin]

Crossposted at: Adding Missing Dates to a Table

I've mentioned that original forum already in a comment.

 

[Magdoulin]

I've mentioned that original forum already in a comment.

 

[Fluff]

No you supplied a link to Reddit, not to Chandoo or to ExcelGuru.
You should provide links to All sites, not just one.

 

[Magdoulin]

G

No you supplied a link to Reddit, not to Chandoo or to ExcelGuru.
You should provide links to All sites, not just one.

Got it, I thought that I needed to post the original one only
Thanks.

 

[Fluff]

Can you please supply all the other links.