Reputation: 641
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!
Upvotes: 2
Views: 1000
Reputation: 30344
The following should work for you. I have named your sample data query as Table.
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.
Upvotes: 2