Search code examples
powerbidaxpowerquerypowerbi-desktopm

PowerBI: copy previous dates values in case of missing dates

I have a rather large table in PowerBI that looks as follows:

Date1 ID1 ID2 Date2 Amount1 Amount2 Amount3
04.02.2022 1234 12 04.02.2022 5 3 8
04.02.2022 1234 13 04.02.2022 5 3 8
04.02.2022 1235 14 04.02.2022 6 3 9
06.02.2022 1234 10 06.02.2022 20 23 46
06.02.2022 1238 11 06.02.2022 20 23 46
06.02.2022 1238 14 06.02.2022 26 23 49

As in the case above, if e.g. 05.02.2022 is missing, I would like my end result to look like

Date1 ID1 ID2 Date2 Amount1 Amount2 Amount3
04.02.2022 1234 12 04.02.2022 5 3 8
04.02.2022 1234 13 04.02.2022 5 3 8
04.02.2022 1235 14 04.02.2022 6 3 9
05.02.2022 1234 12 05.02.2022 5 3 8
05.02.2022 1234 13 05.02.2022 5 3 8
05.02.2022 1235 14 05.02.2022 6 3 9
06.02.2022 1234 10 06.02.2022 20 23 46
06.02.2022 1238 11 06.02.2022 20 23 46
06.02.2022 1238 14 06.02.2022 26 23 49

Which means that everything from 04.02.2022 is copy pasted, just with a new date, 05.02.2022.

There are also cases where no data is available for 2 or 3 days, so in those instances I would need the all data from the last known date, until we have data again.

Does someone know how to implement this in PowerBI?

Thank you!

Solution

  • The following should work for you. I have named your sample data query as Table.

    enter image description here

    Create a new query and paste in the following code. This new query refers to your sample data query named Table so you will have two queries.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDRMzDSMzIwMlKK1QFyTVG5ZghuLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Date"}, Table, {"Date1"}, "Table", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Count", each if Table.RowCount([Table]) > 0 then [Table] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Count"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom", each if Table.RowCount([Table]) > 0 then [Table] else Table.ReplaceValue([Count],[Count]{0}[Date1],[Date],Replacer.ReplaceValue,{"Date1", "Date2"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Table", "Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date1", "ID1", "ID2", "Date2", "Amount1", "Amount2", "Amount3"}, {"Date1", "ID1", "ID2", "Date2", "Amount1", "Amount2", "Amount3"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Date"})
in
    #"Removed Columns1"

    If you need to fill out more dates, then just change the date range in step 1 which you should be able to auto generate depending on your data. Mine looks like this.

    enter image description here