Forum Discussion

cassidy's avatar
cassidy
Power Participant
6 years ago
Solved

Fill in rows between dates

I've been trying to figure out this puzzle in power query for a week or so, not finding any examples that apply to my situation.     Our database only records the change in price of a product on th...
  • edhans's avatar
    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 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: