Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
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?
Proud to be a Super User! | |
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.
Proud to be a Super User! | |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
41 | |
23 | |
21 | |
21 | |
13 |