Getting value from another column according to last occurrence

Question

I'm trying to create a column that tells me whether an entry is still valid or not. For that I want to use several criteria, one being date and another being if it has been canceled or not.
I have one column with reference code, another with entry, when the same reference has more than one instance, then a new entry number is assigned, and finally a third column tells me what the new isntance is about.
I wish to obtain the values on the third column of the last instance of each reference, compare them to 3 "cancellation" options and finally add the date criteria.
If I do this on PQ, every row is marked, so that means that every instance is marked differently, but if I do it on a worksheet, I obtain the desired results but applying a combination of formulas (some of it is in Spanish): IF(OR(INDEX(Tabla1[#Todo];ROW(XLOOKUP([@Reference];Tabla1[[#Todo];[Reference]];Tabla1[[#Todo];[Entry]];"ERROR";0;-1));COLUMN(Tabla1[[#Encabezados];[Tipo de Movimiento]]))=Cancelaciones[[#Todo];[Tipo de Movimiento]]);"NO";SI([@[Vigencia Hasta]]-TODAY()<0;"NO";"SI")).
I wish to be able to do the same with M code directly on PQ since I have over 200k records, and growing, on my database, so relying on the combination of formulas on the worksheet is out of the question.
Please find example file attached. I cant upload the xlsx file, hope the txt helps.
The data has been modified and simplyfied, no confidential data inside, but still representative of my issue.
Thanks in advance43039-example.txt

Answer 1

Hi @Shinka

EDIT: Code posted on Sat. 28th had a mistake, revised below. Apologies

Sorting on [Vigencia Hasta] was an idea but this won't work for certain cases, look at the one in blue:

I had to rework the logic partly due to your yearly [Poliza] with monthly [Nro. Endoso]s but not only...
In this demo I combined data from your 1st and 2nd samples (just to get more scenarios) and applied the new logic that produces the same results as your formula(1)

Note that I had to bring your list of cancelations ("Cancelacion","Cancelación Corto Plazo","Cancelación Plurianual") into the picture. Check the results in the demo and if that's OK replace (in the previous version of the query I posted) the Steps after #"Columnas combinadas" with:

    Cancelations = List.Buffer({"Cancelacion","Cancelación Corto Plazo","Cancelación Plurianual"}),  
    GroupedByPoliza = Table.Group(#"Columnas combinadas", {"Poliza"},  
        {"NestedPolizaRecords", each  
            let  
                MaxDate = List.Max([Vigencia Hasta]),  
                TipoMovimientoMaxDate = Table.SelectRows(_, each [Vigencia Hasta] = MaxDate),  
                Vigente =  
                    if Table.Last(TipoMovimientoMaxDate)[Vigencia Hasta] < Date.From(DateTime.LocalNow())  
                        then false            // NO VIGENTE  
                    else if List.ContainsAny(TipoMovimientoMaxDate[Tipo de Movimiento], Cancelations)  
                        then false            // NO VIGENTE  
                    else true,                // VIGENTE  
                EndosoZeroAtTop = Table.Combine({  
                    Table.AddColumn(Table.SelectRows(_, each [Nro. Endoso] = 0),  
                        "Vigencia", each "VIGENTE", type text  
                    ),  
                    Table.AddColumn(Table.SelectRows(_, each [Nro. Endoso] <> 0),  
                        "Vigencia", each "NO VIGENTE", type text  
                    )  
                })  
            in  
                if Vigente then EndosoZeroAtTop  
                else Table.AddColumn(_, "Vigencia", each "NO VIGENTE", type text),  
            type table  
        }  
    ),  
    CombinedNestedTables = Table.Combine(GroupedByPoliza[NestedPolizaRecords])  
in  
    CombinedNestedTables  

(1) Your formula assumes the Table starts in row #1...

Answer 2

Try the following (hope I didn't make a mistake somewhere)

NB: In the sample you provided, records for the same Reference/Poliza were ordered (sorted) by Entry/Nro. Endoso. If this isn't the case at Step Columnas combinadas we will need to make an adjustment in the GroupedByPoliza step:

let
    Origen = Folder.Files("ORIGIN ROUTE"),
    #"Archivos ocultos filtrados1" = Table.SelectRows(Origen, each [Attributes]?[Hidden]? <> true),
    #"Invocar función personalizada1" = Table.AddColumn(#"Archivos ocultos filtrados1", "Transformar archivo", each #"Transformar archivo"([Content])),
    #"Columnas con nombre cambiado1" = Table.RenameColumns(#"Invocar función personalizada1", {"Name", "Source.Name"}),
    #"Otras columnas quitadas1" = Table.SelectColumns(#"Columnas con nombre cambiado1", {"Source.Name", "Transformar archivo"}),
    #"Se expandió Transformar archivo" = Table.ExpandTableColumn(#"Otras columnas quitadas1", "Transformar archivo",
        {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12",
        "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23",
        "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34",
        "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43"}
    ),
    #"Filas filtradas" = Table.SelectRows(#"Se expandió Transformar archivo", each [Column3] <> null and [Column3] <> ""),
    #"Encabezados promovidos" = Table.PromoteHeaders(#"Filas filtradas", [PromoteAllScalars=true]),
    #"Filas filtradas1" = Table.SelectRows(#"Encabezados promovidos", each ([Tipo de Documento] <> "Tipo de Documento")),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Filas filtradas1",{{"Tipo de movimiento", "Tipo de movimiento.1"}}),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas con nombre cambiado",
        {{"Vigencia Hasta", type date}, {"Tipo de Documento", type text}, {"Número de Documento", type text},
        {"Código de Producto", Int64.Type}, {"Rama", type text}, {"Producto", type text}, {"Nro. Endoso", Int64.Type},
        {"Usuario", type text}, {"Fecha Emisión", type date}, {"Vigencia Desde", type date}, {"Moneda Emisión", type text},
        {"Cant. de Cuotas", Int64.Type}, {"Prima", Currency.Type}, {"Recargo Financiero", Currency.Type},
        {"Otras Cargas", Currency.Type}, {"IVA", Currency.Type}, {"Prima Total", Currency.Type}, {"Suma Asegurada", Currency.Type},
        {"Fronting", type text}, {"Tipo de Movimiento", type text}, {"Tipo de Endoso", type text}, {"Tomador", Int64.Type},
        {"Nombre Tomador", type text}, {"Tipo de movimiento.1", type text}, {"Medio de transporte", type text},
        {"Fecha de Operación", type date}, {"Tipo Operación", type text}, {"Actividad del Riesgo", type text}, {"Agente", Int64.Type},
        {"Nombre Agente", type text}, {"Importe Comisión", Currency.Type}, {"Tipo Cambio", Currency.Type}, {"Total Prima Cedida", Currency.Type},
        {"Total Suma Asegurada Cedida", Currency.Type}, {"Compañía", type text}, {"% de fronting fee", Percentage.Type},
        {"% de comisión Reaseguro", Percentage.Type}, {"% Impuestos (Fronting/Reas)", Percentage.Type}, {"Monto de fronting fee", Currency.Type},
        {"Monto de comisión Reaseguro", Currency.Type}, {"Monto Impuestos (Fronting/Reas)", Currency.Type}}
    ),
    #"Columna duplicada" = Table.DuplicateColumn(#"Tipo cambiado", "Código Rama", "Código Rama - Copia"),
    #"Columnas combinadas" = Table.CombineColumns(
        Table.TransformColumnTypes(#"Columna duplicada", 
            {{"Código Rama - Copia", type text}, {"Póliza", type text}}, "es-UY"
        ),
        {"Código Rama - Copia", "Póliza"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),
        "Poliza"
    ),
     GroupedByPoliza = Table.Group(#"Columnas combinadas", {"Poliza"},
         {"NestedPolizaRecords", each
             let
                 // Required fields (only) from the last record in the current table
                 reqFields = Record.SelectFields(Table.Last(_),
                     {"Vigencia Hasta","Tipo de Movimiento"}
                 )
             in
                 Table.AddColumn(_, "Vigencia", each
                     if Text.StartsWith(reqFields[Tipo de Movimiento],"Cancelac")
                         then "NO VIGENTE"
                     else if Duration.Days(reqFields[Vigencia Hasta]-Date.From(DateTime.LocalNow())) >=0
                         then "VIGENTE" else "NO VIGENTE",
                     type text
                 ),
             type table
         }
     ),
     CombinedNestedTables = Table.Combine(GroupedByPoliza[NestedPolizaRecords])
 in
     CombinedNestedTables

Answer 3

@Shinka

Hopefully you should be good with:

    Cancelations = List.Buffer({"Cancelacion","Cancelación Corto Plazo","Cancelación Plurianual"}),  
    GroupedByPoliza = Table.Group(#"Columnas combinadas", {"Poliza"},  
        {"NestedPolizaRecords", each  
            let  
                MaxDate = List.Max([Vigencia Hasta]),  
                TipoMovimientoMaxDate = Table.SelectRows(_, each [Vigencia Hasta] = MaxDate),  
                Vigente =  
                    if Table.Last(TipoMovimientoMaxDate)[Vigencia Hasta] < Date.From(DateTime.LocalNow())  
                        then "NO VIGENTE"  
                    else if List.ContainsAny(TipoMovimientoMaxDate[Tipo de Movimiento], Cancelations)  
                        then "NO VIGENTE"  
                    else "VIGENTE"  
            in  
                Table.AddColumn(_, "Vigencia", each Vigente, type text),  
            type table  
        }  
    ),  
    CombinedNestedTables = Table.Combine(GroupedByPoliza[NestedPolizaRecords])  
in  
    CombinedNestedTables  

Answer 4

Hi @Shinka

Assuming your Tabla1 in Excel:

let  
    Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],  
    ChangedTypes = Table.TransformColumnTypes(Source,  
        {{"Reference", type text}, {"Entry", Int64.Type}, {"Fecha Emisión", type date},  
         {"Vigencia Desde", type date}, {"Vigencia Hasta", type date},  
         {"Tipo de Movimiento", type text}}  
    ),  
    GroupedByRef = Table.Group(ChangedTypes, {"Reference"},  
        {"REF", each  
            let  
                // Required fields (only) from the last record in the current table  
                reqFields = Record.SelectFields(Table.Last(_),  
                    {"Vigencia Hasta","Tipo de Movimiento"}  
                )  
            in  
                Table.AddColumn(_, "STATUS", each  
                    if Text.StartsWith(reqFields[Tipo de Movimiento],"Cancelac")  
                        then "NO VIGENTE"  
                    else if Duration.Days(reqFields[Vigencia Hasta]-Date.From(DateTime.LocalNow())) >=0  
                        then "VIGENTE" else "NO VIGENTE",  
                    type text  
                ),  
            type table  
        }  
    ),  
    CombinedNestedTables = Table.Combine(GroupedByRef[REF])  
in  
    CombinedNestedTables  

Check this does what you want. If it doesn't please create - in this sample - a column next to [STATUS] that reflects what you actually expect. On the other hand if this solves your problem please mark this post as Accepted Answer to help others with a similar challenge - Thanks in advance & nice day...

Note: In the sample I changed a couple of your values & created a few other records for testing purpose

Answer 5

Hi @Lz._
Thank you so much for you answer! Unfortunately, I couldn't make it work.

I should have clarified that the data is already imported into PQ. I'm not working with a table in an worksheet and then importing it into PQ, but rather grabbing 5 excel files from a folder and importing them into PQ. Then transform the data and finally trying to add the extra column with the desired outcome.
So I tried your code in my original file from line 9 by using the "add custom column" in the GUI and pasting the code in the prompt.

This is what the code looks like so far in the advanced editor (Note: changed a part of it by incorporating the Text.StartsWith formula in your code whichI find very lean and much more efficient):

----------

let
Origen = Folder.Files("ORIGIN ROUTE"),
#"Archivos ocultos filtrados1" = Table.SelectRows(Origen, each [Attributes]?[Hidden]? <> true),
#"Invocar función personalizada1" = Table.AddColumn(#"Archivos ocultos filtrados1", "Transformar archivo", each #"Transformar archivo"([Content])),
#"Columnas con nombre cambiado1" = Table.RenameColumns(#"Invocar función personalizada1", {"Name", "Source.Name"}),
#"Otras columnas quitadas1" = Table.SelectColumns(#"Columnas con nombre cambiado1", {"Source.Name", "Transformar archivo"}),
#"Se expandió Transformar archivo" = Table.ExpandTableColumn(#"Otras columnas quitadas1", "Transformar archivo", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43"}),
#"Filas filtradas" = Table.SelectRows(#"Se expandió Transformar archivo", each [Column3] <> null and [Column3] <> ""),
#"Encabezados promovidos" = Table.PromoteHeaders(#"Filas filtradas", [PromoteAllScalars=true]),
#"Filas filtradas1" = Table.SelectRows(#"Encabezados promovidos", each ([Tipo de Documento] <> "Tipo de Documento")),
#"Columnas con nombre cambiado" = Table.RenameColumns(#"Filas filtradas1",{{"Tipo de movimiento", "Tipo de movimiento.1"}}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas con nombre cambiado",{{"Vigencia Hasta", type date}, {"Tipo de Documento", type text}, {"Número de Documento", type text}, {"Código de Producto", Int64.Type}, {"Rama", type text}, {"Producto", type text}, {"Nro. Endoso", Int64.Type}, {"Usuario", type text}, {"Fecha Emisión", type date}, {"Vigencia Desde", type date}, {"Moneda Emisión", type text}, {"Cant. de Cuotas", Int64.Type}, {"Prima", Currency.Type}, {"Recargo Financiero", Currency.Type}, {"Otras Cargas", Currency.Type}, {"IVA", Currency.Type}, {"Prima Total", Currency.Type}, {"Suma Asegurada", Currency.Type}, {"Fronting", type text}, {"Tipo de Movimiento", type text}, {"Tipo de Endoso", type text}, {"Tomador", Int64.Type}, {"Nombre Tomador", type text}, {"Tipo de movimiento.1", type text}, {"Medio de transporte", type text}, {"Fecha de Operación", type date}, {"Tipo Operación", type text}, {"Actividad del Riesgo", type text}, {"Agente", Int64.Type}, {"Nombre Agente", type text}, {"Importe Comisión", Currency.Type}, {"Tipo Cambio", Currency.Type}, {"Total Prima Cedida", Currency.Type}, {"Total Suma Asegurada Cedida", Currency.Type}, {"Compañía", type text}, {"% de fronting fee", Percentage.Type}, {"% de comisión Reaseguro", Percentage.Type}, {"% Impuestos (Fronting/Reas)", Percentage.Type}, {"Monto de fronting fee", Currency.Type}, {"Monto de comisión Reaseguro", Currency.Type}, {"Monto Impuestos (Fronting/Reas)", Currency.Type}}),
#"Columna duplicada" = Table.DuplicateColumn(#"Tipo cambiado", "Código Rama", "Código Rama - Copia"),
#"Columnas combinadas" = Table.CombineColumns(Table.TransformColumnTypes(#"Columna duplicada", {{"Código Rama - Copia", type text}, {"Póliza", type text}}, "es-UY"),{"Código Rama - Copia", "Póliza"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Poliza"),
Vigencia = Table.AddColumn(#"Columnas combinadas", "Vigencia", each if Text.StartsWith([Tipo de Movimiento], "Cancelac") then "NO VIGENTE" else
if Duration.Days([Vigencia Hasta] - Date.From(DateTime.LocalNow())) >=0 then "VIGENTE" else "NO VIGENTE"),
#"Tipo cambiado1" = Table.TransformColumnTypes(Vigencia,{{"Vigencia", type text}})
in
#"Tipo cambiado1"

----------

Couldn't write on the sample file you shared either to show the expected outcome.