Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pedroccamaraDBI
Post Partisan
Post Partisan

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.

apagar.JPG

Can you help?
Thanks in advance

6 REPLIES 6
kirete17
Frequent Visitor

FYI

kirete17_0-1667459066803.png

 

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

 

wdx223_Daniel
Super User
Super User

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?

jbwtp
Memorable Member
Memorable Member

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 lot

Hi @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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors