Excel Power Query: Adding Missing Dates to a Table

M

Magdoulin

New member
Joined
Aug 28, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2016
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


Code: 
[FONT='inherit']let[/FONT]
[FONT='inherit']Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],[/FONT]
[FONT='inherit']#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Schedule", type text}}),[/FONT]
[FONT='inherit']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"}),[/FONT]
[FONT='inherit']Merged = Table.NestedJoin(Dates, {"Date"}, #"Changed Type", {"Date"}, "Gr", JoinKind.LeftOuter),[/FONT]
[FONT='inherit']Expanded = Table.ExpandTableColumn(Merged, "Gr", {"Name", "Schedule"}, {"Name", "Schedule"}),[/FONT]
[FONT='inherit']Reordered = Table.ReorderColumns(Expanded,{"Name", "Date", "Schedule"}),[/FONT]
[FONT='inherit']#"Filled Down" = Table.FillDown(Reordered,{"Name"}),[/FONT]
[FONT='inherit']#"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,"Weekend",Replacer.ReplaceValue,{"Schedule"}),[/FONT]
[FONT='inherit']#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Name", Order.Ascending}, {"Date", Order.Ascending}})[/FONT]
[FONT='inherit']in[/FONT]
[FONT='inherit']#"Sorted Rows"[/FONT]


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
​
 
Welcome to the forum. :)

Posting on multiple sites is OK by us, however we do expect you to tell us you have done so.

On this occasion I'll provide links for you (please do this yourself in future):
https://www.mrexcel.com/board/threads/adding-missing-dates-to-a-table.1180390/
https://www.reddit.com/r/PowerQuery/comments/pc8w8o/adding_missing_dates_to_a_table/
https://chandoo.org/forum/threads/adding-missing-dates-to-a-table.46842/#post-278571


I have also added code tags to your post, as required.

Please attach a workbook that demonstrates the issue you wish to address.
 
Last edited by a moderator:
In the attached a query and a function (code below).
The max and min dates are per each name, not the min/max of the whole table.
I've not replaced nulls with 'Weekend' because if missing dates are week days this could get confusing.
I've done no sorting for the final table.
Please take note of the cross-posting links requirement.

Code:
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Schedule", type text}}),
    GroupedRows = Table.Group(ChangedType, {"Name"}, {{"Grpd", each _, type table [Name=nullable text, Date=nullable date, Schedule=nullable text]}}),
    InvokedCustomFunction = Table.AddColumn(GroupedRows, "Custom", each fnFillDates([Grpd])),
    RemovedColumns = Table.RemoveColumns(InvokedCustomFunction,{"Grpd"}),
    ExpandedTable = Table.ExpandTableColumn(RemovedColumns, "Custom", {"AllDates", "SomeDates"}, {"AllDates", "SomeDates"}),
    ExpandedSomeDates = Table.ExpandTableColumn(ExpandedTable, "SomeDates", {"Schedule"}, {"Schedule"}),
    ChangedType1 = Table.TransformColumnTypes(ExpandedSomeDates,{{"AllDates", type date}, {"Schedule", type text}})
in
    ChangedType1
Code: 
(tbl)=>
let
    AllDs=List.Dates(List.Min(tbl[Date]),Number.From(List.Max(tbl[Date]))-Number.From(List.Min(tbl[Date]))+1,#duration(1,0,0,0)),
    ConvertedtoTable = Table.FromList(AllDs, Splitter.SplitByNothing(), {"AllDates"}, null, ExtraValues.Error),
    MergedQueries = Table.NestedJoin(ConvertedtoTable, {"AllDates"}, tbl, {"Date"}, "SomeDates", JoinKind.LeftOuter)
in
    MergedQueries
 

Attachments

  • ExcelGuru11293.xlsx
    18.7 KB · Views: 12
I was planning to reply my post with the links on the other forums but I found it pending waiting for approval so I couldn't do that till it's approved.

I'm afraid another post is pending now and I forgot that the forum pend the newly added post, I was planning to add the links to the reply as well, apologies.
 
You must log in or register to reply here.
Back
Top