Forum Discussion

MarkDonald's avatar
MarkDonald
Regular Visitor
2 years ago
Solved

Repeat Friday Data for Weekends and Holidays

Hi - have searched and found similar problems, but no suggestions I have been able to follow...   I have data which is posted for each business day, while nothing at all appears for non business da...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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"

     

     

     

     

     

     

AlienSx's avatar
AlienSx
Super User
2 years ago

Hello, MarkDonald 

let
    Source = your_table,
    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
MarkDonald's avatar
MarkDonald
Regular Visitor
2 years ago

Thankyou so much!  Works perfectly, and I almost understand what it is doing 😁