Inserting rows for missing dates based on multiple values from other tables

Hi mikemarr3 

You can create three blank queries and put the following code to advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slPTizJzM9TcFSK1UHiOqFwixWclWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Locations = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Locations", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1)
in
    #"Added Custom"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lHyyU9OLMnMz1NwBHIMlWJ1QLJGeGWN8cqaYpU1gMqa4dVrjlcvppud8LrZCa+bnZBMNsEri+kjJ7xuhuqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Location = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Location", type text}, {"Status", Int64.Type}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJWitUBcYyQOcbIHBNkjikyxwyZYw7mKCjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dates = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, Query1, {"Custom"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Locations"}, {"Locations"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Added Custom", {"Dates", "Locations"}, Query2, {"Date", "Location"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries1", "Table", {"Status"}, {"Status"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table",{{"Status", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"Unlocked",Replacer.ReplaceValue,{"Status"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Locations", Order.Ascending}, {"Dates", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Custom"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly