Solved: Re: Create IF Statement to find nearest date based...

SHS · ‎05-18-2022

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! 🙂

Vijay_A_Verma · ‎05-19-2022

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"

View solution in original post

Vijay_A_Verma · ‎05-19-2022

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"

SHS · ‎05-24-2022

Hi @Vijay_A_Verma

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

Vijay_A_Verma · ‎05-24-2022

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.

SHS · ‎05-30-2022

Hi @Vijay_A_Verma

Have you found the issue in your/my modified m-code?

Looking forward to your reply!

SHS · ‎05-25-2022

Hi @Vijay_A_Verma

It is the code that creates some sort of issue.

I have attached the m-code and a sample table for your reference. Thank you very much for your support on this

let
Source = Csv.Document(File.Contents("P:\Global Sales Excellence\Power BI\MASTER-Files\View103_PI01_prdh01and04.csv"),[Delimiter=";", Columns=11, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Valid From", type date}, {"Valid To", type date}, {"Pricing Unit", Int64.Type}, {"Amount", type number}}),

//Only include 60 (Released) and 70 (Released - Only Service) - 0-59 not relevant, 75 + 76 we do not have an actual price before contacting the vendor and therefor we cannot create historical comparisons//
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([MaterialStatus] = "60" or [MaterialStatus] = "70")),
//Requested to only look at internal sales price (PI01) with a Valid To Date in or after 01.2021//
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Valid To] > #date(2020, 12, 31)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Plant", "Material", "Valid From", "Valid To", "Amount", "Currency", "Pricing Unit"}),

//Created Identifier as materials may have prices for both plants. To ensure wrong date is not returned when comparing cost price, identifier is created to ensure right link//
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Material + Plant", each [Material] & "_" & [Plant]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Material + Plant", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Material + Plant", Order.Ascending}}),

//Duplicated "Valid To" to keep original SAP Date for later comparison - LOGIC to be included in "Valid To (LOGIC)" in agreement with TNI - logic description in subsequent note(s)//
#"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "Valid To", "Valid To - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Valid To - Copy", "Valid To (SAP)"}}),

//Logic defined to ensure periods are "filled" - i.e., in cases we with product missing a PI01 Price in specific periods, the Valid To date is updated to the day before the next pricing update//
//Logic Example: 1000012 has a PI01 Price for Plant 1110 01.01.2020-01.03.2020 and then an update 01.05.2020-01.10.2020 - for these cases, 01.03.2020 (Valid To from first appearance) should be updated to 30.04.2020//
//The logic is not practically correct, as missing periods will result in the price no longer returning when inserting the material to a sales order - yet it is needed to ensure the price development calculation before and after these periods are not returning infinity (0-divisions)//
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.ReplaceValue(#"Added Index",each [Valid To],each try if #"Added Index"[Valid From]{[Index]+1}<>Date.AddDays([Valid To],1) and #"Added Index"[#"Material + Plant"]{[Index]+1}=[#"Material + Plant"] then Date.AddDays(#"Added Index"[Valid From]{[Index]+1},-1) else [Valid To] otherwise [Valid To],Replacer.ReplaceValue,{"Valid To"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Valid To", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Valid To", "Valid To (LOGIC)"}})

in
#"Renamed Columns"

Application	Condition Type	Plant	Material	Valid From	Valid To	Amount	Currency	Pricing Unit	Unit	MaterialStatus
V	PI01	1110	1017948	########	########	161,13	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	161,11	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,83	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,85	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,89	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,71	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,54	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,47	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,45	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,41	DKK	1	PAA	90

SHS · ‎05-24-2022

Hi @Vijay_A_Verma

I tried this and it worked, so it seems to be the code. I have attached a sample reference and my code for your reference. Thank you for your support!

Application	Condition Type	Plant	Material	Valid From	Valid To	Amount	Currency	Pricing Unit	Unit	MaterialStatus
V	PI01	1110	1017948	########	########	161,13	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	161,11	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,83	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,85	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,89	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,71	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,54	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,47	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,45	DKK	1	PAA	90
V	PI01	1110	1017948	########	########	162,41	DKK	1	PAA	90

let

Source = Csv.Document(File.Contents(),[Delimiter=";", Columns=11, Encoding=65001, QuoteStyle=QuoteStyle.None]),

#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Valid From", type date}, {"Valid To", type date}, {"Pricing Unit", Int64.Type}, {"Amount", type number}}),

//Only include 60 (Released) and 70 (Released - Only Service) - 0-59 not relevant, 75 + 76 we do not have an actual price before contacting the vendor and therefor we cannot create historical comparisons//

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([MaterialStatus] = "60" or [MaterialStatus] = "70")),

//Requested to only look at internal sales price (PI01) with a Valid To Date in or after 01.2021//

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Valid To] > #date(2020, 12, 31)),

#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Plant", "Material", "Valid From", "Valid To", "Amount", "Currency", "Pricing Unit"}),

//Created Identifier as materials may have prices for both plants. To ensure wrong date is not returned when comparing cost price, identifier is created to ensure right link//

#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Material + Plant", each [Material] & "_" & [Plant]),

#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Material + Plant", type text}}),

#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Material + Plant", Order.Ascending}}),

//Duplicated "Valid To" to keep original SAP Date for later comparison - LOGIC to be included in "Valid To (LOGIC)" in agreement with TNI - logic description in subsequent note(s)//

#"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "Valid To", "Valid To - Copy"),

#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Valid To - Copy", "Valid To (SAP)"}}),

//Logic defined to ensure periods are "filled" - i.e., in cases we with product missing a PI01 Price in specific periods, the Valid To date is updated to the day before the next pricing update//

//Logic Example: 1000012 has a PI01 Price for Plant 1110 01.01.2020-01.03.2020 and then an update 01.05.2020-01.10.2020 - for these cases, 01.03.2020 (Valid To from first appearance) should be updated to 30.04.2020//

//The logic is not practically correct, as missing periods will result in the price no longer returning when inserting the material to a sales order - yet it is needed to ensure the price development calculation before and after these periods are not returning infinity (0-divisions)//

#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),

#"Added Custom1" = Table.ReplaceValue(#"Added Index",each [Valid To],each try if #"Added Index"[Valid From]{[Index]+1}<>Date.AddDays([Valid To],1) and #"Added Index"[#"Material + Plant"]{[Index]+1}=[#"Material + Plant"] then Date.AddDays(#"Added Index"[Valid From]{[Index]+1},-1) else [Valid To] otherwise [Valid To],Replacer.ReplaceValue,{"Valid To"}),

#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Valid To", type date}}),

#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Valid To", "Valid To (LOGIC)"}})

in

#"Renamed Columns"

SHS · ‎05-23-2022

Hi @Vijay_A_Verma

You are truly a Power BI Wizard!

Thank you so much for above!

Best

Sebastian

Create IF Statement to find nearest date based on Unique Key (Power Query)

Helpful resources

FabCon Global Hackathon

Power BI Monthly Update - October 2025

FabCon Atlanta 2026

FabCon is coming to Atlanta

Create IF Statement to find nearest date based on Unique Key (Power Query)

Helpful resources

FabCon Global Hackathon

Power BI Monthly Update - October 2025

FabCon Atlanta 2026