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

[mikemarr3]

Hey all,

I have a table that lets me know a status for a location based on a date. This status is either Locked, or Unlocked, however the row record only populates the first time a location is locked. It then updates subsequently every time it is unlocked and relocked. 

In the data 1 = Locked and 0 = Unlocked. 

Im trying to create a report where i can search by date range and across locations to see what locations are currently unlocked.

Since I dont get the data unless a loctation was locked the first time, I need to create the data based on the fact that its missing from my table to begin with. 

I have 3 tables. The data set itself with the Locks and Unlocks. A table with all possible locations, and a Calendar with all possible dates. 

Ive tried merging queries etc but havnt be able to get anything to output my desired results. 

Some sample Data Below

Lock Status Data 

Date	Location	Status
1/1/2023	Location A	1
1/2/2023	Location A	1
1/3/2023	Location A	1
1/5/2023	Location A	0
1/6/2023	Location A	1
1/7/2023	Location A	0
1/1/2023	Location B	1
1/2/2023	Location B	1
1/3/2023	Location B	0
1/4/2023	Location B	0
1/5/2023	Location B	1
1/7/2023	Location B	0

Possible Location Data 

Locations

Location A

Location B

Locations C

Calendar of all dates in range 

Dates

1/1/2023

1/2/2023

1/3/2023

1/4/2023

1/5/2023

1/6/2023

1/7/2023

Desired output. 

Date	Location	Status
1/1/2023	Location A	1
1/2/2023	Location A	1
1/3/2023	Location A	1
1/4/2023	Location A	Unlocked
1/5/2023	Location A	0
1/6/2023	Location A	1
1/7/2023	Location A	0
1/1/2023	Location B	1
1/2/2023	Location B	1
1/3/2023	Location B	0
1/4/2023	Location B	0
1/5/2023	Location B	1
1/6/2023	Location B	Unlocked
1/7/2023	Location B	0
1/1/2023	Location C	Unlocked
1/2/2023	Location C	Unlocked
1/3/2023	Location C	Unlocked
1/4/2023	Location C	Unlocked
1/5/2023	Location C	Unlocked
1/6/2023	Location C	Unlocked
1/7/2023	Location C	Unlocked

[Anonymous · Accepted solution]

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

[ronrsnfld]

Your description is that the status is updated when the lock status changes (after the first lock). But your results table does not show that. It shows the status changing on the missing date from locked to unlocked (status=0 on missing date even when previous date is status=1).

Please clarify the logic.

[HotChilli]

If I understand correctly, you want to generate the 'all possible dates and all possible locations' table first. So, start with the Dates table, Add a custom column which is the only column in Locations table.  This is powerful and straightforward.  I'll leave you to get on with it. Expand the list column -> to New Rows

--

You now have the 'all possible' table.  Merge this table with the Lock Status table (left outer join on columns date and Location.

Expand the column to return data you need.  You can then create the values for Unlocked if there is a null entry on the row.

--

Try that and I will help if you get stuck