Forum Discussion
Repeat Friday Data for Weekends and Holidays
Here is another code that does not have the duplicates problem you ran into:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc25DYAwEETRXja28B7IIDKOLiz33wYrjUjw2ppgghf8Wqlk3rKyGiU6D58/L8zUUo+Xv4zw9tcRPv724T5r/hFNiRFNjRFNAwrPop2iqhIrsqvGim4xau0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [balanceDate = _t, Concat = _t, Balance = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{ {"balanceDate", type date}, {"Concat", type text}, {"Balance", Currency.Type}},"en-150"), //Group by date #"Grouped Rows" = Table.Group(#"Changed Type", {"balanceDate"}, { {"all", each _, type table [balanceDate=nullable date, Concat=nullable text, Balance=Currency.Type]}}), //Create table of All dates #"All Dates" = Table.FromColumns( {List.Dates(#"Grouped Rows"[balanceDate]{0}, Duration.Days(List.Last(#"Grouped Rows"[balanceDate])- #"Grouped Rows"[balanceDate]{0})+1, #duration(1,0,0,0))}, type table[dates=date]), //Join the tables and sort so we have // nulls where there are missing dates #"Join" = Table.Join(#"Grouped Rows","balanceDate",#"All Dates","dates",JoinKind.FullOuter), #"Sorted Rows" = Table.Sort(Join,{{"dates", Order.Ascending}}), //Replace nulls in balanceDate with the missing data #"Replace nulls" = Table.ReplaceValue( #"Sorted Rows", each [balanceDate], (r) as date=> if r[balanceDate]=null then r[dates] else r[balanceDate], Replacer.ReplaceValue, {"balanceDate"} ), #"Removed Columns" = Table.RemoveColumns(#"Replace nulls",{"dates"}), #"Filled Down" = Table.FillDown(#"Removed Columns",{"all"}), #"Expanded all" = Table.ExpandTableColumn(#"Filled Down", "all", {"Concat", "Balance"}) in #"Expanded all"
Actually, sorry, it isn't looking quite right, it seems to be duplicating for the dates that already exist, apart from the last. I have stepped through and can see where it is duplicating the dates but can't really figure out how to make it work without this happening. I can correct at the end by simply removing duplicates so I have something that works, so thanks! This is how I inserted your code:
let
Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"balanceDate", type datetime}, {"Concat", type text}, {"Balance", type number}}),
type_date = Table.TransformColumnTypes(Source,{{"balanceDate", type date}}),
g = Table.Group(type_date, "balanceDate", {{"bd", each _}, {"data", each true}}),
all_dates = List.Dates(List.Min(g[balanceDate]), Duration.TotalDays(List.Max(g[balanceDate]) - List.Min(g[balanceDate])),#duration(1, 0, 0, 0)),
tl = Table.FromColumns({all_dates}, {"balanceDate"}),
combine = g & tl,
sort = Table.Sort(combine,{{"balanceDate", Order.Ascending}, {"data", Order.Descending}}),
f_down = Table.FillDown(sort,{"bd"}),
expand = Table.ExpandTableColumn(f_down, "bd", {"Concat", "Balance"})[[balanceDate], [Concat], [Balance]]
in
expand
balanceDateConcatBalance
| 6/07/2023 | A:A:A | 0 |
| 6/07/2023 | A:A:B | 1 |
| 6/07/2023 | A:A:C | 2 |
| 6/07/2023 | A:A:D | 3 |
| 6/07/2023 | A:A:A | 0 |
| 6/07/2023 | A:A:B | 1 |
| 6/07/2023 | A:A:C | 2 |
| 6/07/2023 | A:A:D | 3 |
| 7/07/2023 | A:A:A | 0 |
| 7/07/2023 | A:A:B | 1 |
| 7/07/2023 | A:A:C | 2 |
| 7/07/2023 | A:A:D | 3 |
| 8/07/2023 | A:A:A | 0 |
| 8/07/2023 | A:A:B | 1.1 |
| 8/07/2023 | A:A:C | 2.2 |
| 8/07/2023 | A:A:D | 3.3 |
| 8/07/2023 | A:A:A | 0 |
| 8/07/2023 | A:A:B | 1.1 |
| 8/07/2023 | A:A:C | 2.2 |
| 8/07/2023 | A:A:D | 3.3 |
| 9/07/2023 | A:A:A | 0 |
| 9/07/2023 | A:A:B | 1.1 |
| 9/07/2023 | A:A:C | 2.2 |
| 9/07/2023 | A:A:D | 3.3 |
| 10/07/2023 | A:A:A | 0 |
| 10/07/2023 | A:A:B | 1.21 |
| 10/07/2023 | A:A:C | 2.42 |
| 10/07/2023 | A:A:D | 3.63 |
Super UserHere is another code that does not have the duplicates problem you ran into:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc25DYAwEETRXja28B7IIDKOLiz33wYrjUjw2ppgghf8Wqlk3rKyGiU6D58/L8zUUo+Xv4zw9tcRPv724T5r/hFNiRFNjRFNAwrPop2iqhIrsqvGim4xau0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [balanceDate = _t, Concat = _t, Balance = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"balanceDate", type date}, {"Concat", type text}, {"Balance", Currency.Type}},"en-150"),
//Group by date
#"Grouped Rows" = Table.Group(#"Changed Type", {"balanceDate"}, {
{"all", each _, type table [balanceDate=nullable date, Concat=nullable text, Balance=Currency.Type]}}),
//Create table of All dates
#"All Dates" = Table.FromColumns(
{List.Dates(#"Grouped Rows"[balanceDate]{0},
Duration.Days(List.Last(#"Grouped Rows"[balanceDate])- #"Grouped Rows"[balanceDate]{0})+1,
#duration(1,0,0,0))},
type table[dates=date]),
//Join the tables and sort so we have
// nulls where there are missing dates
#"Join" = Table.Join(#"Grouped Rows","balanceDate",#"All Dates","dates",JoinKind.FullOuter),
#"Sorted Rows" = Table.Sort(Join,{{"dates", Order.Ascending}}),
//Replace nulls in balanceDate with the missing data
#"Replace nulls" = Table.ReplaceValue(
#"Sorted Rows",
each [balanceDate],
(r) as date=> if r[balanceDate]=null then r[dates] else r[balanceDate],
Replacer.ReplaceValue,
{"balanceDate"}
),
#"Removed Columns" = Table.RemoveColumns(#"Replace nulls",{"dates"}),
#"Filled Down" = Table.FillDown(#"Removed Columns",{"all"}),
#"Expanded all" = Table.ExpandTableColumn(#"Filled Down", "all", {"Concat", "Balance"})
in
#"Expanded all"