Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Product | MSRP | Current_Price | Type | Date |
15233-128 | 72 | 72 | Full Price | 3/4/2020 |
15233-128 | 58 | 58 | Full Price | 2/27/2020 |
15233-128 | 72 | 72 | Full Price | 2/27/2020 |
15233-128 | 72 | 72 | Full Price | 2/20/2020 |
15233-128 | 58 | 58 | Full Price | 1/2/2020 |
15233-EH2 | 72 | 72 | Full Price | 3/4/2020 |
15233-EH2 | 72 | 72 | Full Price | 2/27/2020 |
15233-EH2 | 72 | 72 | Full Price | 2/20/2020 |
15233-EH2 | 58 | 58 | Full Price | 1/31/2020 |
Solved! Go to Solution.
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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCheck-out these instructions to determine which of the two solutions will work best for you. Both solutions posted here are industry best.
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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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 |
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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingWell, 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
@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.