Forum Discussion
Create IF Statement to find nearest date based on Unique Key (Power Query)
Hi All,
I've been asked to create a report in which the development in cost prices for a set of products are compared with the internal sales price offered to the subsidiaries - ideally, to see if the % increase in cost price is transferred to the subsidiaries.
For this, I have 2 datasets; 1 containing all price adjustments for cost price since 01.2021, and 1 containing all price adjustments for the internal sales price since 01.2021.
For internal sales prices I have 2 dates: Valid From (the date from which the price was valid/active) and Valid To (the date from which the price was no longer valid/active)
In both datasets, cases exists in which a product may not have been updated every month and these are the one's I'm struggling with.
Challenge 1
Internal Sales Price Table
Product Price Valid From Valid To
Product 1 1.000USD 01-01-2021 01-02-2021
Product 1 1.200USD 01-05-2021 01-10-2021
In above case, I would like Product 1/Row 1 Valid To to be updated to 30-04-2021 - so the closest date to the nearest valid from for the specific product.
Preferably I would like to do it through Power Query, but I'm open for all suggestions that could help!
Challenge 2
In the Cost Price Table the issue is much similar to above, but rather than having 2 dates, I simply have a valid from date
Cost Price Table
Product Price Valid From
Product 1 500USD 01-01-2021
Product 1 550USD 01-04-2021
In above case, I would like the price from 01-01-2021 to be copied into new rows equal to the number of months missing - so in above, 2 new rows to be added (for 02.2021 and 03.2021 both with 500USD as Price).
Is this possible?
Looking forward to any support! 🙂
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
Challenge 1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlEwVNJRMtQzMDAIDXYBMfUN9Y0MjECiRjBmrA66ciOYclOEckMDPOpNYcYb6ZvANRjpG5li6jCC6DCG6jBG2GCGzQKIchNTM4hyM4RyC9zK4e5HOBrMNjKG6ogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Price = _t, #"Valid From" = _t, #"Valid To" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Price", type text}, {"Valid From", type date}, {"Valid To", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), Custom = Table.ReplaceValue(#"Added Index",each [Valid To],each try if #"Added Index"[Valid From]{[Index]+1}<>Date.AddDays([Valid To],1) and #"Added Index"[Product]{[Index]+1}=[Product] then Date.AddDays(#"Added Index"[Valid From]{[Index]+1},-1) else [Valid To] otherwise [Valid To],Replacer.ReplaceValue,{"Valid To"}), #"Removed Columns" = Table.RemoveColumns(Custom,{"Index"}) in #"Removed Columns"Challenge 2
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlEwVNJRMjUwCA12ATIM9Q31jQyMDJViddBUmEJVmOBWYQhRYYZFhRHIbAM8RkAUQE2wgCuIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Price = _t, #"Valid From" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Price", type text}, {"Valid From", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), Custom = Table.ReplaceValue(#"Added Index",each [Valid From],each try if #"Added Index"[Product]{[Index]+1}=[Product] then [a=#"Added Index"[Valid From]{[Index]+1}, b=List.Generate(()=>[x=[Valid From]], each [x]<a, each [x=Date.AddMonths([x],1)], each [x])][b] else {[Valid From]} otherwise {[Valid From]},Replacer.ReplaceValue,{"Valid From"}), #"Removed Columns" = Table.RemoveColumns(Custom,{"Index"}), #"Expanded Valid From" = Table.ExpandListColumn(#"Removed Columns", "Valid From") in #"Expanded Valid From"
7 Replies
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
Challenge 1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlEwVNJRMtQzMDAIDXYBMfUN9Y0MjECiRjBmrA66ciOYclOEckMDPOpNYcYb6ZvANRjpG5li6jCC6DCG6jBG2GCGzQKIchNTM4hyM4RyC9zK4e5HOBrMNjKG6ogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Price = _t, #"Valid From" = _t, #"Valid To" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Price", type text}, {"Valid From", type date}, {"Valid To", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), Custom = Table.ReplaceValue(#"Added Index",each [Valid To],each try if #"Added Index"[Valid From]{[Index]+1}<>Date.AddDays([Valid To],1) and #"Added Index"[Product]{[Index]+1}=[Product] then Date.AddDays(#"Added Index"[Valid From]{[Index]+1},-1) else [Valid To] otherwise [Valid To],Replacer.ReplaceValue,{"Valid To"}), #"Removed Columns" = Table.RemoveColumns(Custom,{"Index"}) in #"Removed Columns"Challenge 2
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlEwVNJRMjUwCA12ATIM9Q31jQyMDJViddBUmEJVmOBWYQhRYYZFhRHIbAM8RkAUQE2wgCuIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Price = _t, #"Valid From" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Price", type text}, {"Valid From", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), Custom = Table.ReplaceValue(#"Added Index",each [Valid From],each try if #"Added Index"[Product]{[Index]+1}=[Product] then [a=#"Added Index"[Valid From]{[Index]+1}, b=List.Generate(()=>[x=[Valid From]], each [x]<a, each [x=Date.AddMonths([x],1)], each [x])][b] else {[Valid From]} otherwise {[Valid From]},Replacer.ReplaceValue,{"Valid From"}), #"Removed Columns" = Table.RemoveColumns(Custom,{"Index"}), #"Expanded Valid From" = Table.ExpandListColumn(#"Removed Columns", "Valid From") in #"Expanded Valid From"Maybe I was a bit too fast on this one. No question that the logic works (Challenge 1), I can see that in Power Query. However, when I try to load this into the file, the dataset went from 1,4 MB to min. 6,6 GB (I cancelled the loading) - How can this logic result in such a huge change in datafile size?
I have inserted my m-code for reference
Just comment or remove last 4 lines before in and remove 1 from Renamed columns1 which is after in. This will remove changes suggested by me.
Then load your query and see the size and report back the finding to me.
If your problem disappears, then my code has some issue. I will need 2 to 3 lines of your csv and your complete query for me to simulate your issue.
