Forum Discussion
Post ProdigyFill rows with no records for each order
Hi experts!
I have a huge transaction table that contains for each order multple rows. This table get's new entries if the state of a order was changed:
| Order Number | State | Date Changed |
| 111 | 1 | 01.01.2020 |
| 111 | 3 | 10.01.2020 |
| 111 | 5 | 15.01.2020 |
As you can see I have now entries/ records for 02.01.2020 for instance.
Since I want to count in a visual at the end the number of orders with state < 5 I will get no results for 14.01.2020 as there is no entry.
How can I supplement this table with records for each order between the max and end date?
Or how would you do that?
Whatever I understand from your post, you need this - See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRAmN9Q30jAyMDpVgdmLAxRNgAXdwUIm6KLG4ENcZI3xhd2BQsbIQibgxXbgSzNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t, State = _t, #"Date Changed" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Number", Int64.Type}, {"State", Int64.Type}, {"Date Changed", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if #"Added Index"[Order Number]{[Index]+1}=[Order Number] then List.Dates([Date Changed],Duration.Days(#"Added Index"[Date Changed]{[Index]+1}-[Date Changed])+1,#duration(1,0,0,0)) else {[Date Changed]} otherwise {[Date Changed]}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date Changed", "Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Date Changed"}}) in #"Renamed Columns"
1 Reply
- Vijay_A_Verma
Most Valuable Professional
Whatever I understand from your post, you need this - See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRAmN9Q30jAyMDpVgdmLAxRNgAXdwUIm6KLG4ENcZI3xhd2BQsbIQibgxXbgSzNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t, State = _t, #"Date Changed" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Number", Int64.Type}, {"State", Int64.Type}, {"Date Changed", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if #"Added Index"[Order Number]{[Index]+1}=[Order Number] then List.Dates([Date Changed],Duration.Days(#"Added Index"[Date Changed]{[Index]+1}-[Date Changed])+1,#duration(1,0,0,0)) else {[Date Changed]} otherwise {[Date Changed]}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date Changed", "Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Date Changed"}}) in #"Renamed Columns"