Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cassidy
Power Participant
Power Participant

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 the day it happens, which is great, but I also need a historical output with every date for at least the last year to see change over time.  Final output would be every product, every date, with appropriate values. 

 

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 far, I have been able to merge in a full calendar to create new lines with null values and then use Fill Down to copy the values down.  This is almost perfect.  Problem is that when the product changes ( Line 5 & 6 / red & green  ), I end up filling down the prior products values in as the new products start date values.  Only when I get to the new products first real value does it correct itself.

 

Thanks for your help

 

ProductMSRPCurrent_PriceTypeDate
15233-1287272Full Price3/4/2020
15233-1285858Full Price2/27/2020
15233-1287272Full Price2/27/2020
15233-1287272Full Price2/20/2020
15233-1285858Full Price1/2/2020
15233-EH27272Full Price3/4/2020
15233-EH27272Full Price2/27/2020
15233-EH27272Full Price2/20/2020
15233-EH25858Full Price1/31/2020
1 ACCEPTED SOLUTION
edhans
Super User
Super User

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:

2020-04-06 19_46_05-Untitled - Power Query Editor.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
Divergence_One
New Member

Check-out these instructions to determine which of the two solutions will work best for you. Both solutions posted here are industry best.

edhans
Super User
Super User

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:

2020-04-06 19_46_05-Untitled - Power Query Editor.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
cassidy
Power Participant
Power Participant

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     
ProductMSRPCurrent_PriceTypeDate Range ProductMSRPCurrent_PriceTypeDate Range 
60399-G432819Clearance3/16/2020 60399-G432819Clearance3/16/2020*Date Price Changed
60399-G432819Clearance3/15/2020 60399-G432828Full Price3/15/2020 
60399-G432819Clearance3/14/2020 60399-G432828Full Price3/14/2020 
60399-G432819Clearance3/13/2020 60399-G432828Full Price3/13/2020 
60399-G432819Clearance3/12/2020 60399-G432828Full Price3/12/2020 
60399-G432819Clearance3/11/2020 60399-G432828Full Price3/11/2020 
60399-G432828Full Price3/10/2020 60399-G432828Full Price3/10/2020*Date Price Changed
60399-G432828Full Price3/9/2020 60399-G432814Discount3/9/2020 
60399-G432828Full Price3/8/2020 60399-G432814Discount3/8/2020 
60399-G432828Full Price3/7/2020 60399-G432814Discount3/7/2020 
60399-G432814Discount3/6/2020 60399-G432814Discount3/6/2020*Date Price Changed

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
cassidy
Power Participant
Power 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!

 

Great @cassidy! Glad it worked. Sorry I misunderstood, but glad my code was useful enough that you just tweaked it to get what you needed.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
dax
Community Support
Community Support

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.

 

Greg_Deckler
Super User
Super User

@ImkeF and @edhans probably know better but I'm guessing that this will involve List.Generate in order to get a full list of Dates and then some sort of Merge and a custom column.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors