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.
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. Some of it is in Spanish, hope it doesnt get in the way. The data has been modified and simplyfied, no confidential data inside, but still representative of my issue.
Thanks in advance
No file attached, please try again.
If you can provide the worksheet formulas as well, will be helpful.
My apologies. How about now?
Thanks
You can try the attached file, there are 2 queries. One with a step by step approach to get the result, the other with 1 formula only, but it basically combines all steps into one, it's not a different solution. The first version might be easier to follow and understand each step formula.
>code>
let
Cancelaciones= Excel.CurrentWorkbook(){[Name="Cancelaciones"]}[Content][Tipo de Movimiento],
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "LastOccurrence", each List.PositionOf( Source[Reference],[Reference],Occurrence.Last)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "LastOccurrenceTipo", each Source[Tipo de Movimiento]{[LastOccurrence]}),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Check", each if List.Contains(Cancelaciones,[LastOccurrenceTipo]) then "No" else if Number.From([Vigencia Hasta])-Number.From(Date.From(DateTime.LocalNow()))>=0 then "Si" else "No")
in
#"Added Custom2"
Your code looks really nice and lean, but I've been unable to work it into my query. Below the code I have so far (A lot of it is in Spanish, sorry, can't help it. Hope it's not much trouble...):
_______________________
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"}),
#"Columna de tabla expandida1" = Table.ExpandTableColumn(#"Otras columnas quitadas1", "Transformar archivo", Table.ColumnNames(#"Transformar archivo"(#"Archivo de ejemplo"))),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Columna de tabla expandida1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type any}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type any}, {"Column29", type text}, {"Column30", type text}, {"Column31", type any}, {"Column32", type text}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type text}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}}),
#"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each [Column1] <> null and [Column1] <> ""),
#"Encabezados promovidos" = Table.PromoteHeaders(#"Filas filtradas", [PromoteAllScalars=true]),
#"Columnas con nombre cambiado" = Table.RenameColumns(#"Encabezados promovidos",{{"Tipo de movimiento", "Tipo de movimiento.1"}}),
#"Filas filtradas1" = Table.SelectRows(#"Columnas con nombre cambiado", each ([Tipo de Documento] <> "Tipo de Documento")),
#"Columna duplicada" = Table.DuplicateColumn(#"Filas filtradas1", "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"),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Columnas combinadas",{{"2014.xlsx", type text}, {"Tipo de Documento", type text}, {"Número de Documento", type text}, {"Código Rama", Int64.Type}, {"Código de Producto", Int64.Type}, {"Nro. Endoso", Int64.Type}, {"Fecha Emisión", type date}, {"Vigencia Desde", type date}, {"Vigencia Hasta", type date}, {"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}, {"Tomador", Int64.Type}, {"Fecha de Operación", type date}, {"Agente", Int64.Type}, {"Importe Comisión", Currency.Type}, {"Tipo Cambio", Currency.Type}, {"Total Prima Cedida", Currency.Type}, {"Total Suma Asegurada Cedida", Currency.Type}, {"% 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}}),
#"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado1", "Vigencia", each if [Tipo de Movimiento] = "Cancelacion" then "NO VIGENTE" else
if [Tipo de Movimiento] = "Cancelación Corto Plazo" then "NO VIGENTE" else
if [Tipo de Movimiento] = "Cancelación Plurianual" then "NO VIGENTE" else
if Duration.Days([Vigencia Hasta] - Date.From(DateTime.LocalNow())) >=0 then "VIGENTE" else "NO VIGENTE"),
#"Tipo cambiado2" = Table.TransformColumnTypes(#"Personalizada agregada",{{"Vigencia", type text}})
in
#"Tipo cambiado2"
_________________
As you can see, I'm using PQ to pull together several files from a folder. The "Cancelaciones" table was a parcial solution so that I could use the worksheet formula, but the idea is to perform the entire operation on PQ. I've tried adding your code from step #"Personalizada agregada", changing the column names to match what I have, but it I haven't been able to make it work...
Do you see any way to make it work?
Thanks in advance.
Build a list in PQ if you don't want to use the table.
The final part of the code should be:
#"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado1", "Vigencia", each if List.Contains(Cancelaciones,#"Tipo cambiado1"[Tipo de Movimiento]{List.PositionOf( #"Tipo cambiado1"[Reference],[Reference],Occurrence.Last)}) then "NO VIGENTE" else if Number.From([Vigencia Hasta])-Number.From(Date.From(DateTime.LocalNow()))>=0 then "VIGENTE" else "NO VIGENTE" ),
#"Tipo cambiado2" = Table.TransformColumnTypes(#"Personalizada agregada",{{"Vigencia", type text}})
in
#"Tipo cambiado2"
The "Cancelaciones" table was a parcial solution so that I could use the worksheet formula, but the idea is to perform the entire operation on PQ.
If we load the list from that table, this does not mean that we are not performing the operation entirely in PQ, it's just a way to feed the values rather than editing the query each time you need to add something to the list.
i am not seeing the Reference column, you will have to replace this with the actual name of that column.
Hi Catalin,
This is how the code looks like now:
____________
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"}),
#"Columna de tabla expandida1" = Table.ExpandTableColumn(#"Otras columnas quitadas1", "Transformar archivo", Table.ColumnNames(#"Transformar archivo"(#"Archivo de ejemplo"))),
#"Filas filtradas" = Table.SelectRows(#"Columna de tabla expandida1", each [Column1] <> null and [Column1] <> ""),
#"Encabezados promovidos" = Table.PromoteHeaders(#"Filas filtradas", [PromoteAllScalars=true]),
#"Columnas con nombre cambiado" = Table.RenameColumns(#"Encabezados promovidos",{{"Tipo de movimiento", "Tipo de movimiento.1"}}),
#"Filas filtradas1" = Table.SelectRows(#"Columnas con nombre cambiado", each ([Tipo de Documento] <> "Tipo de Documento")),
#"Columna duplicada" = Table.DuplicateColumn(#"Filas filtradas1", "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"),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Columnas combinadas",{{"2014.xlsx", type text}, {"Tipo de Documento", type text}, {"Número de Documento", type text}, {"Código Rama", Int64.Type}, {"Código de Producto", Int64.Type}, {"Nro. Endoso", Int64.Type}, {"Fecha Emisión", type date}, {"Vigencia Desde", type date}, {"Vigencia Hasta", type date}, {"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}, {"Tomador", Int64.Type}, {"Fecha de Operación", type date}, {"Agente", Int64.Type}, {"Importe Comisión", Currency.Type}, {"Tipo Cambio", Currency.Type}, {"Total Prima Cedida", Currency.Type}, {"Total Suma Asegurada Cedida", Currency.Type}, {"% 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}, {"Rama", type text}, {"Producto", type text}, {"Usuario", type text}, {"Moneda Emisión", type text}, {"Fronting", type text}, {"Tipo de Movimiento", type text}, {"Tipo de Endoso", type text}, {"Nombre Tomador", type text}, {"Tipo de movimiento.1", type text}, {"Medio de transporte", type text}, {"Tipo Operación", type text}, {"Actividad del Riesgo", type text}, {"Nombre Agente", type text}, {"Compañía", type text}}),
Cancelaciones={"Cancelacion", "Cancelación Corto Plazo", "Cancelación Plurianual"},
#"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado1", "Vigencia", each if List.Contains(Cancelaciones,#"Tipo cambiado1"[Tipo de Movimiento]{List.PositionOf( #"Tipo cambiado1"[Poliza],[Poliza],Occurrence.Last)}) then "NO VIGENTE" else if Number.From([Vigencia Hasta])-Number.From(Date.From(DateTime.LocalNow()))>=0 then "VIGENTE" else "NO VIGENTE" ),
#"Tipo cambiado2" = Table.TransformColumnTypes(#"Personalizada agregada",{{"Vigencia", type text}})
in
#"Tipo cambiado2"
___________________
Unfortunately, it never finishes loading. It keep loading each file on the folder over and over again and multiplying their seize by a tremendous factor. Files that are 10MB or less keep loading in PQ reporting seizes of over 600MB.... and it simply doesnt stop. It doesn't seem to be stuck, just constantly loading.
The [Reference] Column is actually called [Poliza]. It's the resulting column from step #"Columnas combinadas".
Try with less files, see what happens.
Try with a custom solution to get data from folder, the automatic process you have used has many issues and it's not as flexible as you think.
Here is an example, with csv files: https://www.myonlinetraininghub.com/excel-forum/power-query/how-to-remove-duplicated-headers-when-importing-from-folder
You can use the example to build a version that reads from xlsx files, not just csv.
