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"
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.
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"
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.
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!