Repeat Friday Data for Weekends and Holidays

[MarkDonald]

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 days.  I want to copy the data such that is is repeated for the following non business days, with the non business day date.

I have more than one row of data so a simple fill down isn't possible.  I can create another table with all dates, and merge with the original table to show the missing dates, but haven't managed to succesfully get any further than that.  I am pretty new to Power Query, so I might struggle with anything too complicated!

Any help is much appreciated.

My sample raw data is here: 

balanceDate	Concat	Balance
6/07/2023	A:A:A	0.00
6/07/2023	A:A:B	1.00
6/07/2023	A:A:C	2.00
6/07/2023	A:A:D	3.00
8/07/2023	A:A:A	0.00
8/07/2023	A:A:B	1.10
8/07/2023	A:A:C	2.20
8/07/2023	A:A:D	3.30
10/07/2023	A:A:A	0.00
10/07/2023	A:A:B	1.21
10/07/2023	A:A:C	2.42
10/07/2023	A:A:D	3.63

and the desired outcome is here:

balanceDate	Concat	Balance
6/07/2023	A:A:A	0.00
6/07/2023	A:A:B	1.00
6/07/2023	A:A:C	2.00
6/07/2023	A:A:D	3.00
7/07/2023	A:A:A	0.00
7/07/2023	A:A:B	1.00
7/07/2023	A:A:C	2.00
7/07/2023	A:A:D	3.00
8/07/2023	A:A:A	0.00
8/07/2023	A:A:B	1.10
8/07/2023	A:A:C	2.20
8/07/2023	A:A:D	3.30
9/07/2023	A:A:A	0.00
9/07/2023	A:A:B	1.10
9/07/2023	A:A:C	2.20
9/07/2023	A:A:D	3.30
10/07/2023	A:A:A	0.00
10/07/2023	A:A:B	1.21
10/07/2023	A:A:C	2.42
10/07/2023	A:A:D	3.63

[MarkDonald]

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

[ronrsnfld · Accepted solution]

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"

[MarkDonald]

Thanks - I am just having difficulty merging this into my code as I have already had to get it into the format posted above. At present I have (after removing a few steps): let Source = Sql.Databases("xx"), xxx = Source{[Name="xxx"]}[Data], findur_viewBalanceSheetHistoric = edw{[Schema="findur",Item="viewBalanceSheetHistoric"]}[Data], #"Filtered Rows" = Table.SelectRows(findur_viewBalanceSheetHistoric, each [balanceDate] >= #date(2023, 6, 29) and [balanceDate] <= #date(2023, 7, 17)), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith([portfolio], "ZZZ")), #"Merged Columns" = Table.CombineColumns(#"Filtered Rows1",{"accountGroup", "reportPortfolio", "postingCurrency"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Concat"), #"Grouped Rows" = Table.Group(#"Merged Columns", {"balanceDate", "Concat"}, {{"Balance", each List.Sum([balanceBase]), type nullable number}}) in #"Grouped Rows"

[ronrsnfld]

I don't understand what you mean. My code starts with your sample raw data so you would just replace my Source statement with the part of your code that produces your raw data.

Obviously, you might have to also make changes in column names if you didn't show the real ones in your sample.

I would also suggest that you write your code in a better format than what you show. I think you will find it easier to understand than using the "stream of consciousness" type format you have posted here. At least I would.

[MarkDonald]

Sorry - novice at this, code is what I get from the steps I am taking to clean the data before it gets to a point where I want to replicate the data for the missing dates.  If I just take your code and change the source , I am going to be replicating huge amounts of data for the missing dates that I don't need.  So I am looking to perform the steps in my code below first, and I am trying to figure out how to integrate your code when doing that.  I can't just paste from:

let _t = ((type nullable text) meta [Serialized.Text = true])

as this is part of your source statement (I think, apologies for incorrect terminology).

So my code is below, hopefully better formatted now, if you are able to adapt it to integrate your code after my #"Grouped Rows" = that would be amazing!

let
    Source = Sql.Databases("xx"),
    xxx = Source{[Name="xxx"]}[Data],
    BS = edw{[Schema="findur",Item="viewBalanceSheetHistoric"]}[Data],
    #"Filtered Rows" = Table.SelectRows(findur_viewBalanceSheetHistoric, each [balanceDate] >= #date(2023, 6, 29) and [balanceDate] <= #date(2023, 7, 17)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith([portfolio], "ZZZ")),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"balanceDate", "Concat"}, {{"Balance", each List.Sum([balanceBase]), type nullable number}})

in
    #"Grouped Rows"

[ronrsnfld]

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]

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!

[AlienSx]

@MarkDonald in the end we just select the columns we need 

[[balanceDate], [Concat], [Balance]]

 other than this it's all in the code. Just walk through steps in PQ editor. Refer to MS site to get understanding. 

[AlienSx]

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]

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