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 ...

Need Help

Vijay_A_Verma

4 years ago

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"

Vijay_A_Verma

Most Valuable Professional

4 years ago

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

Resolver I

4 years ago

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

Forum Discussion

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

Related content

Merge statement creates duplicates - Low shuffle merge as cause?

OR Statement ?

SQL Statement opcion error

lookup nearest date

Support for MERGE statement