Power Query / Power bi Si el ID y la fecha de la fila actual son los mismos que los de la fila anterior, la fila de destino es 0 más 1

@ saleha1994

Primero puede agregar una columna de índice y luego la siguiente columna personalizada. Verifique el archivo adjunto.

let p =  Table.Last( Table.SelectRows(#"Added Index", (t2)=> t2[Index] < t[Index] )) in 
Number.From((if p=null then 0 else p[ID] = t[ID]  and p[DATE] = t[DATE])=false)

Puede haber formas más ordenadas de hacerlo, pero de todos modos puedes probar con cualquiera de las siguientes …

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lFyVIrVIVHACZeAEbqAMUzAGYtALAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, ID = _t]),
    TargetCol = let l=List.Transform(Table.ToRows(Source), each Text.Combine(_,", ")), n=List.Count(l)-1 in List.Transform({0..n}, each try Number.From(l{_}<>l{_-1}) otherwise 1),
    FINAL = Table.FromColumns( Table.ToColumns(Source)&{TargetCol}, Table.ColumnNames(Source)&{"TARGET"} )
in
    FINAL

o

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lFyVIrVIVHACZeAEbqAMUzAGYtALAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, ID = _t]),
    Grouped = let cols = Table.ColumnNames(Source) in Table.Group(Source, cols , {{"Gr", each  Table.FromColumns( Table.ToColumns(_)&{{1}&List.Repeat({0}, Table.RowCount(_)-1)}, cols & {"TARGET"} ), type table }}),
    Custom1 = Table.SelectColumns(Grouped, {"Gr"} ),
    FINAL = Table.ExpandTableColumn(Custom1, "Gr", Table.ColumnNames(Custom1[Gr]{0}))
in
    FINAL

o

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lFyVIrVIVHACZeAEbqAMUzAGYtALAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, ID = _t]),
    MergedCols = Table.CombineColumns(Source,Table.ColumnNames(Source),Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged"),
    Index = Table.AddIndexColumn(MergedCols, "Index", 0, 1, Int64.Type),
    AddedTARGET = Table.AddColumn(Index, "TARGET", each try Number.From( MergedCols[Merged]{[Index]-1} <> MergedCols[Merged]{[Index]}) otherwise 1),
    RemovedIndex = Table.RemoveColumns(AddedTARGET,{"Index"}),
    FINAL = Table.SplitColumn(RemovedIndex, "Merged", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), Table.ColumnNames(Source))
in
    FINAL