Forum Discussion
Fill in rows between dates
- 6 years ago
See if this works. I think it does, but not 100% sure I've properly accounted for the duplicate dates you have, like Feb 27.
Paste this into a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Donelet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1MjbWNTSyUNJRMjeCEW6lOTkKAUWZyalAjrG+ib6RgZGBUqwOqnpTOIGi3kjfyBy7BhwWkKPBgCQnGeoboat39TAiyct41WPzASENGD6AaMDpA2NDqIZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, MSRP = _t, Current_Price = _t, Type = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"MSRP", Int64.Type}, {"Current_Price", Int64.Type}, {"Type", type text}, {"Date", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Product", "Date"}, {"Product.1", "Date.1"}), #"Filled Down" = Table.FillDown(#"Expanded Added Index1",{"Product.1", "Date.1"}), #"Added Date Range" = Table.AddColumn(#"Filled Down", "Date Range", each if [Product] = [Product.1] and [Date] > [Date.1] then {Number.From(Date.AddDays([Date.1], 1))..Number.From([Date])} else {Number.From([Date])}), #"Expanded Date Range" = Table.ExpandListColumn(#"Added Date Range", "Date Range"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date Range", type date}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Product", "MSRP", "Current_Price", "Type", "Date Range"}), #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Product", Order.Ascending}, {"Date Range", Order.Descending}}) in #"Sorted Rows"The sorting in the last step isn't necessary for the computer, but it is for me to read the data and ensure the result looked reasonable. 😁
The end result has 98 records, but here is a sample of it:
Hi cassidy ,
I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.