Adding new rows to a table based on previousday value

[Anonymous]

Hello,

I have a table that records the daily stocks from each user, however days in which the user did not input the stock values, I need this omitted dates to be filled by the previous day value per user per product id.

sample data

userid	productid	value	date
1	a	6,433	8/18/2022
1	b	1,874	8/18/2022
1	a	1,358	8/22/2022
2	a	1,256	8/18/2022
2	b	3,050	8/19/2022
1	b	10	8/23/2022
1	a	472	8/23/2022
2	b	1,382	8/23/2022
2	a	152	8/25/2022

Expected Result

userid	productid	value	date
1	a	6433	8/18/2022
1	a	6433	8/19/2022
1	a	6433	8/20/2022
1	a	6433	8/21/2022
1	a	1358	8/22/2022
1	a	472	8/23/2022
1	a	472	8/24/2022
1	a	472	8/25/2022
1	b	1874	8/18/2022
1	b	1874	8/19/2022
1	b	1874	8/20/2022
1	b	1874	8/21/2022
1	b	1874	8/22/2022
1	b	10	8/23/2022
1	b	10	8/24/2022
1	b	10	8/25/2022
2	a	1,256	8/18/2022
2	a	1,256	8/19/2022
2	a	1,256	8/20/2022
2	a	1,256	8/21/2022
2	a	1,256	8/22/2022
2	a	1,256	8/23/2022
2	a	1,256	8/24/2022
2	a	152	8/25/2022
2	b	3,050	8/19/2022
2	b	3,050	8/20/2022
2	b	3,050	8/21/2022
2	b	3,050	8/22/2022
2	b	1,382	8/23/2022

Thank you @Vijay_A_Verma  and @Jimmy801 

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could duplicate a table as TableB, then grroup by the min date , and add yestoday date column.

Then merge column from original table and TableB,

Last sort and fill down.

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9JCsAgDAXQqxTXgeqPqelZxEV7/0N0ULB1CGQRePwkMRpnyBx3L7k28sz3qKvTFRYwiTI6K3KkwQ/R8UUs+iKgIrQIsnVJ+K9jsmIz2qc3lbzswNOznvIBHUP7IusYNVlOCpPC0gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [userid = _t, productid = _t, value = _t, date = _t]),
    #"Grouped Rows" = Table.Group(Source, {"userid", "productid"}, { {"min", each List.Min([date]), type nullable text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "max", each Date.From( DateTime.LocalNow())-#duration(1,0,0,0)),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"userid", "productid", "min", "max"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Reordered Columns", {{"min", type date}, {"max", type date}}, "en-US"),
    Custom1 = Table.AddColumn(#"Changed Type with Locale", "date", each {Number.From([min])..Number.From([max])}
),
    #"Expanded Custom" = Table.ExpandListColumn(Custom1, "date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"min", "max"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"userid", "productid", "date"}, Table, {"userid", "productid", "date"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"value"}, {"Table.value"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table",{{"userid", Order.Ascending}, {"productid", Order.Ascending}, {"date", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Table.value"})
in
    #"Filled Down"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yalanwu-msft This is fantastic!. Thank you.

[ToddChitt]

That seems sort of impossible. A FULL OUTER JOIN means take ALL ROWS from one table, and ALL ROWS from a second table, and if the dates match, put that data on one row, otherwise use whatever data you have from either row.

So if you have a Calendar table with ALL DATES, how can it be missing dates.

Can you share your Power Query M code, possible some sample data as well?

[Anonymous]

@ToddChitt Thank you,

However I have tried this before but some days are still missing 

[ToddChitt]

Do a FULL OUTER JOIN to a table that lists all dates contiguously. Then expand that so that you have one day for every User/Product combinatin. (It's going to be a lot). Order by User ID and Product. Then do a FILL DOWN transform.