Fill in Date gap with value from most recent date.

[CBWW]

I have a dataset that has the structure of Chart 1. I am trying to graph that data with a dataset that has the structure of Chart 2. How would I convert Chart 1 to Chart 2's format with any future value being filled in by the most recent values on a particular date.

For context the data in chart 1 represents somewhat of a target value for the data I am trying to chart it with. So seeing any variance from that target value is my end goal.

The problem I'm having so far is that when I plot Chart 1 with the other data set on a line chart the counts are just being summed for all dates. For instance below is what I'm seeing on the line chart essentially. The values displayed are just the sum of all existing values for a UnitID.

UnitID	Date/Time	Count x	Count y	Count z
123	1/1/2024	5	17	15
123	1/2/2024	5	17	15
123	1/3/2024	5	17	15
123	1/4/2024	5	17	15

Chart 1:

UnitID	Date/Time	Count x	Count y	Count z
123	1/1/2024	2	8	7
123	1/4/2024	3	9	8
124	1/2/2024	9	7	6
124	1/8/2024	3	5	6

Chart 2:

UnitID	Date/Time	Count x	Count y	Count z
123	1/1/2024	2	8	7
123	1/2/2024	2	8	7
123	1/3/2024	2	8	7
123	1/4/2024	3	9	8
123	1/5/2024	3	9	8
124	1/2/2024	9	7	6
124	1/3/2024	9	7	6
124	1/4/2024	9	7	6
124	1/5/2024	9	7	6
124	1/6/2024	9	7	6
124	1/7/2024	9	7	6
124	1/8/2024	3	5	6
124	1/9/2024	3	5	6
124	1/10/2024	3	5	6

[Anonymous]

Hi,@CBWW I am glad to help you.
After talking about it with other members of my team, we worked out the correct Power Query M code
Hope this helps (no need to set up any loops anymore)
Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs3LLPF0UdJRckksSdUPycxNBbKd80vzShQq4KxKOKtKKVYnWsnQyBgoYqhvqG9kYGQCZBoBsQUQm6NIm8CkQVxLsBKItAlY2ggmbQnWqaNkhiJtgazbFEPa0AAmbwa13FIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"UnitID", Int64.Type}, {"Date/Time", type date}, {"Count x", Int64.Type}, {"Count y", Int64.Type}, {"Count z", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"UnitID"}, {{"start date", each List.Min([#"Date/Time"]), type nullable date}, {"end date", each List.Max([#"Date/Time"]), type nullable date}, {"all data", each Table.Sort(_,{"Date/Time", Order.Ascending})}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Dates([start date],Duration.Days([end date]-[start date])+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each let curDate = [Custom] in Table.LastN(Table.SelectRows([all data], each [#"Date/Time"]<=curDate),1)) 
,
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Count x", "Count y", "Count z"}, {"Count x", "Count y", "Count z"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.1",{{"Custom", "Date/Time"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"start date", "end date", "all data"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Date/Time", type datetime}})
in
    #"Changed Type2"

Here is the test data:

We consider that for a UNITID, there may be multiple event blanking intervals, based on the real data that you later provide
You can see that for UNITID124, there are three [Date/Time], not two as generally believed, which is also consistent with your real data.(1/2/2024,1/8/2024,1/10/2024)

Here is the test result:

You can fulfill your second requirement by adding multiple slicers
I've uploaded the modified pbix file for you to check out, I think it should work for your complex data requirements.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[Anonymous]

Hello,@SachinNandanwar ,thanks for your concern about this issue. And I would like to share some additional solutions below.
Hi,@CBWW .I am glad to help you.
Regarding the data you want to populate, I recommend you to implement it in Power query
Here is my test
This is my original data:

Here's the final effect:

Above is the answer to your second question: how to populate values that do not exist, based on UnitID and date to populate data that does not exist.

Here is a suggestion for your first question
As you can see, when I put the values on the Y-axis, the power BI has to show the aggregated values.
And when dealing with multiple UnitIDs, it will directly aggregate the results of different UnitIDs together.

So my suggestion for this case is to add a slicer (which is a common solution to this problem) or use “Small multiples”
like this:

Or add a slicer:

Here is the M code which I create

let
    // first query
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs3LLPF0UdJRckksSdUPycxNBbKd80vzShQq4KxKOKtKKVYnWsnQyBgoYqhvqG9kYGQCZBoBsQUQm6NIm8CkQVxLsBKItAlY2ggmbQnWqaNkhiJtgazbFCIdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source1,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"UnitID", Int64.Type}, {"Date/Time", type date}, {"Count x", Int64.Type}, {"Count y", Int64.Type}, {"Count z", Int64.Type}}),

    // second query
    startdate_123 = #date(2024, 1, 1),
    enddate_123 = #date(2024, 1, 5),
    numberofday_123 = Duration.Days(Duration.From(enddate_123 - startdate_123)) + 1,
    datelist_123 = List.Dates(startdate_123, numberofday_123, #duration(1, 0, 0, 0)),
    datetable_123 = Table.FromList(datelist_123, Splitter.SplitByNothing(), {"date1"}, null, ExtraValues.Error),
    #"Changed Type3" = Table.TransformColumnTypes(datetable_123,{{"date1", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type3", "UnitID", each 123),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom1", {"date1", "UnitID"}, #"Changed Type2", {"Date/Time", "UnitID"}, "Test", JoinKind.LeftOuter),
    #"Added Index1" = Table.AddIndexColumn(#"Merged Queries1", "Index", 1, 1, Int64.Type),
    #"Expanded Test1" = Table.ExpandTableColumn(#"Added Index1", "Test", {"Count x", "Count y", "Count z"}, {"Test.Count x", "Test.Count y", "Test.Count z"}),
    #"Filled Down1" = Table.FillDown(#"Expanded Test1",{"Test.Count x", "Test.Count y", "Test.Count z"}),
    All_Date123 = #"Filled Down1",

    // third query
    startdate_124 = #date(2024, 1, 2),
    enddate_124 = #date(2024, 1, 10),
    numberofday_124 = Duration.Days(Duration.From(enddate_124 - startdate_124)) + 1,
    datelist_124 = List.Dates(startdate_124, numberofday_124, #duration(1, 0, 0, 0)),
    datetable_124 = Table.FromList(datelist_124, Splitter.SplitByNothing(), {"date1"}, null, ExtraValues.Error),
    #"Changed Type4" = Table.TransformColumnTypes(datetable_124,{{"date1", type date}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type4", "UnitID", each 124),
    #"Merged Queries2" = Table.NestedJoin(#"Added Custom2", {"UnitID", "date1"}, #"Changed Type2", {"UnitID", "Date/Time"}, "Test", JoinKind.LeftOuter),
    #"Added Index2" = Table.AddIndexColumn(#"Merged Queries2", "Index", 1, 1, Int64.Type),
    #"Expanded Test2" = Table.ExpandTableColumn(#"Added Index2", "Test", {"Count x", "Count y", "Count z"}, {"Test.Count x", "Test.Count y", "Test.Count z"}),
    #"Filled Down2" = Table.FillDown(#"Expanded Test2",{"Test.Count x", "Test.Count y", "Test.Count z"}),
    All_Date124 = #"Filled Down2",

    // fourth query
    CombinedSource = Table.Combine({All_Date123, All_Date124}),
    #"Removed Columns" = Table.RemoveColumns(CombinedSource,{"Index"})
in
    #"Removed Columns"

I uploaded the test file, and I also retained the distribution of the data conversion code, you can click on the steps in turn to view
To be precise, it is to first create the complete date column of the final display, then append the query, (to avoid that the expansion may be disordered, resulting in errors in filling data, you need to first sort the list correctly), select Filled Down to auto-fill, and finally merge the target result table into a final table!
It should be noted that you need to follow your real data and the situation to choose the right approach for you, as I produced the data may not be consistent with your real data, so you need to write your code according to the actual need to avoid errors (although the idea is correct)
I hope my test can bring you good inspiration.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CBWW]

Thank you for your help. I'm afraid my dataset it too vast to add slicers on a Unit ID level and of course I would need my end date to run perpetually for all unit IDs. Here is a sample of my data

[Anonymous]

Hi,@CBWW .Thank you for your reply.
It looks like your data is very large for this situation. I would recommend that you implement a traversal loop approach to data manipulation in Power Query that fills in the blanks for each UNITID. Unfortunately I'm not very good at Power Query data manipulation and can't give you constructive advice. However, I would like to help you out. Here are some links to Power Query related issues that I have found and resolved.
URL:

List.Generate() and Looping in PowerQuery - Exceed
Solved: Syntax for Looping through a List in an M function - Microsoft Fabric Community
Iterating Over Calculated Tables using Power Query – SQLServerCentral
 

In fact, the processing logic is similar to the processing of the case data I provided above, by using each UNITID as a loop body to process

2. For your second doubt: your data is very complex, if you need to show your data for each UNIT, you can add a date filtering slicer, first select the date, narrow the range of UNITID slicer, which can reduce the complexity of the operation.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[SachinNandanwar]

How do you determine the last date for a given UnitID ?

[CBWW]

My last date would just be the most recent chronological entry for each unit ID until another entry is made for that UnitID. Is that what you're asking?