Forum Discussion
Replace date value if conditions met
Hi guys
My table has this columns :
Date (dd/mm/yyyy), Company name, Category, Sub Category, Product and Sale.
I would like to replace the dates (15/10/2022 replace with 16/10/2022) of the first records that, dates = 15/10/2022 but only for Company = A, and total sale is between 500 and 600.
In this example, those 3 lines should be the one to change the date from 15/10/2022 to 16/10/2022.
Can you help?
Thanks in advance
6 Replies
Hi pedroccamaraDBI,
the code is a bit heavy, but it does what you want (asuming that the last record is not company A, otherwise the total for 15 Oct & company A is over 600):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTRNzTQNzIwMlLSUXIEYkMLY6VYHaCEKZKEEy4JsA5zMxwSFlgkwEaZmUAkzDAkoDrMMSSMMI1yAUlYGuCQMDPH4SpTLBJgO0yBrooFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Company = _t, Sale = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Company", type text}, {"Sale", Int64.Type}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date", "Control"), #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column", each [Date], (x)=> if x[Date] = #date(2022,10,15) and x[Company] = "A" and List.Sum(Table.SelectRows(#"Duplicated Column", each Record.RemoveFields(_, "Sale") = Record.RemoveFields(x, "Sale"))[Sale])>500 and List.Sum(Table.SelectRows(#"Duplicated Column", each Record.RemoveFields(_, "Sale") = Record.RemoveFields(x, "Sale"))[Sale])<600 then #date(2022,10,16) else [Date] ,Replacer.ReplaceValue,{"Date"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Check", each if [Date]=[Control] then "" else "Changed", type text) in #"Added Custom"Cheers,
John
Hi jbwtp
I think what you did here is adding another column for control, which it turn out to be more secure to know what records did we've change. Could you send me your file? It's better for me to understand what you did, you know?
And the last record is Company "A"....so I don't know if this way it'll work.
Thanks a lotHi pedroccamaraDBI,
I've duplicated the column just to show the change, you can ignore/remove those steps.
Sorry, I can't share the file (the company's IT policy does not permit it).
You can, however, create a Blank Query in the Editor and copy-paste the code in the query, it will demonstrate what the code does. The actual PBI file would not give much more than that.
Re: complany A. In you data sample there are four, not three lines relating to Company=A and Date=15/10/22. Therefore, no lines in the sample satisfies the condition as Company A's sales on 15 Oct are over 600 (almost 700).
Hope this helps.
Cheers,
John
NewStep=Table.FromRecords(List.Combine(Table.Group(PreviousStepName,{"Date","Company"},{"n",each let a=List.Sum([Sales]),b=Table.ToRecords(_) in if a>500 and a<600 and [Company]{0}="A" and [Date]{0}=#date(2022,10,15) then {b{0}&[Date=#date(2022,10,16)]}&List.Skip(b) else b})[n]))
Hi wdx223_Daniel
Thank you so much for your answer. Don't know why but this code is taking forever. All the time evaluating, don't know why. In the meantime, I think it would be best to know what records were changed. Don't you?
FYI
let Source = Excel.CurrentWorkbook(){[Name="่กจ1"]}[Content], #"Change Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), AddIndexColumn = Table.AddIndexColumn(#"Change Type", "Index", 0, 1, Int64.Type), Custom1 = Table.Group( AddIndexColumn, { "Date", "Company" }, { "Q", each if [Date]{0} = #date(2022,10,15) and [Company]{0} = "A" then Table.FromRows( List.Accumulate( Table.ToRows( _ ), { {}, 0 }, (x,y)=> if x{1} + y{2} < 600 then { x{0} & { {#date(2022,10,16)} & List.Skip( y ) }, x{1} + y{2} } else { x{0} & {y}, x{1} + y{2} } ){0}, Table.ColumnNames( _ ) ) else _ } )[Q], Custom2 = Table.RemoveColumns( Table.Sort( Table.Combine( Custom1 ), "Index" ), "Index" ) in Custom2
