Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Post Partisan
3 years ago

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 r...
Need Help
kirete17's avatar
kirete17
Frequent Visitor
3 years ago

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