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:
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 Done
let
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:
Thank you, I think this is very close, but the price changes are going the opposite direction. I played around with the indexes a bit, but wasn't able to make it reverse.
Here is a one product example using your query (Left, Current) and what the goal output would be (Right, Expected). Thanks for the help.
| Current | Expected | ||||||||||
| Product | MSRP | Current_Price | Type | Date Range | Product | MSRP | Current_Price | Type | Date Range | ||
| 60399-G43 | 28 | 19 | Clearance | 3/16/2020 | 60399-G43 | 28 | 19 | Clearance | 3/16/2020 | *Date Price Changed | |
| 60399-G43 | 28 | 19 | Clearance | 3/15/2020 | 60399-G43 | 28 | 28 | Full Price | 3/15/2020 | ||
| 60399-G43 | 28 | 19 | Clearance | 3/14/2020 | 60399-G43 | 28 | 28 | Full Price | 3/14/2020 | ||
| 60399-G43 | 28 | 19 | Clearance | 3/13/2020 | 60399-G43 | 28 | 28 | Full Price | 3/13/2020 | ||
| 60399-G43 | 28 | 19 | Clearance | 3/12/2020 | 60399-G43 | 28 | 28 | Full Price | 3/12/2020 | ||
| 60399-G43 | 28 | 19 | Clearance | 3/11/2020 | 60399-G43 | 28 | 28 | Full Price | 3/11/2020 | ||
| 60399-G43 | 28 | 28 | Full Price | 3/10/2020 | 60399-G43 | 28 | 28 | Full Price | 3/10/2020 | *Date Price Changed | |
| 60399-G43 | 28 | 28 | Full Price | 3/9/2020 | 60399-G43 | 28 | 14 | Discount | 3/9/2020 | ||
| 60399-G43 | 28 | 28 | Full Price | 3/8/2020 | 60399-G43 | 28 | 14 | Discount | 3/8/2020 | ||
| 60399-G43 | 28 | 28 | Full Price | 3/7/2020 | 60399-G43 | 28 | 14 | Discount | 3/7/2020 | ||
| 60399-G43 | 28 | 14 | Discount | 3/6/2020 | 60399-G43 | 28 | 14 | Discount | 3/6/2020 | *Date Price Changed |
- edhans6 years agoCommunity Champion
You asked for this:
"Looks like the table below. For example, Line 1 (orange), I need additional rows below it repeating the same values until it gets to Line 2 (blue). "so I took that to mean you wanted the data in orange copying down until it got to the blue row, then stopping. Is that not accurate?
- cassidy6 years agoPower Participant
Well, yes you are correct, it does look I asked for the wrong thing. But, I was able to edit what you created to work the other way. Thanks for the solution!