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"

     

     

     

     

     

     

ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

I still don't understand your issue. If the last step of your code that produces your raw data table is the step you have named #"Grouped Rows", then, from my code,

  • Delete the "Source =" step.
  • Paste my remaining code to replace everything after your #"Grouped Rows" step.
  • You will now have steps that aren't properly named, so do the following:
    • Change your #"Grouped Rows" named step to something like #"My Last Step"
    • In my first line, which will now be #"Changed Type", change the Table reference from Source to #"My Last Step" 
  • Now the steps should properly refer to each other.
MarkDonald's avatar
MarkDonald
Regular Visitor
2 years ago

OK, thankyou, it just looked to me that the piece of code in your source statement: 

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [balanceDate = _t, Concat = _t, Balance = _t])

looked as if it might be relevant to the code further down the line, but apparently not.  Replaced the names in a few steps and all working great, thanks!