☰

Extracción de todos los valores coincidentes (o cercanos) entre los valores START n-ésimo y END n-ésimo dados/usando Power Query

tag-icon • tag-icon microsoft-excel worksheet-function vba power-query microsoft-excel-365
Extracción de todos los valores coincidentes (o cercanos) entre los valores START n-ésimo y END n-ésimo dados/usando Power Query

Considere tener dos mesas. La Tabla1(T1) tiene columnas de valores de Fecha/Hora y Presión y la Tabla2(T2) también tiene columnas de valores de Fecha/Hora e Información.

T1 muestra el cambio de presión a través del tiempo, por ejemplo: 21.11.2019 19:25:15 con 147 mbar y cambia básicamente por minuto, 21.11.2019 19:26:15 con 198 mbar y así sucesivamente... T2 es ligeramente diferente, se puede observar como una tabla de pares con instancias de INICIO y FIN o en el caso de archivos originales (valores de inicio/finalización). Esos valores se extraen originalmente del archivo de registro de nuestro sistema donde se guardan todos los registros de datos. Es un registro donde puede rastrear lo que está sucediendo dentro del sistema.

Producción: Lo que realmente necesito es tener todos los valores (Fecha/hora y presión (P7)) que se encuentran entre esos valores de INICIO y FIN, que coincidan con esos pares de T2.

Algo como esto: T2

ingrese la descripción de la imagen aquí

Además, cuando escribí "INICIO n-ésimo" y "END valor n-ésimo" en la descripción del encabezado, estoy buscandotener valor del quinto minutocomo valor INICIO, por ejemplo si el INICIO es el 21.11.2019 19:25 con 147 mbar, necesito el 21.11.2019 19:30 con 198 mbar, y como FIN no el último valor sino uno antes de terminar en el mismo período, para El último valor de la instancia es el 21.11.2019 13:00 y necesito uno el 21.11.2019 12:59... Espero que esto tenga sentido para ti, es un poco complicado.

Consulte el SS a continuación para obtener instrucciones y lo que necesito como "quinto minuto".

Explicando el quinto minuto

Cosas a las que prestar atencióny lo que me doy cuenta que está afectando la coincidencia correcta entre tablas son los segundos en la columna Fecha/hora (tanto para T1 como para T2). Mientras no haya coincidencias exactas (Fecha/Hora) entre dos tablas, la consulta falla... ¿hay alguna manera de hacer que busque una coincidencia de minutos y no exactamente de segundos? Si no es un segundo, entonces un minuto... algo así...

El segundo problema puede ocurrir cuando hay más "pares" dentro de un día, como puede observar en el T2, generalmente es un par por día, pero hay y habrá casos en los que habrá más instancias de "inicio/fin" dentro un día.

La tercera cosa a tener en cuenta es que es posible que, aunque tenga pares en T2, no haya valores que se encuentren entre esas fechas de T1 (es un problema interno con un sistema en sí donde no recibimos los datos en cierto período). , pero en ese caso la consulta debería continuar sin dar un error y proporcionar otras coincidencias en el camino.

Estoy proporcionando las consultas que he estado usando pero no me dan resultados adecuados:

primer codigoes muy lento y carga siempre los datos... pero todavía no cumple con su deber.

Mesas: Parámetros => T1 Registro_Original => T2

let Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Parameters"]}[Content]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"P7 [mbar]", Int64.Type}}),
//get start/stop times table
Source2 = Table.Buffer(Excel.CurrentWorkbook(){[Name="Log_Original"]}[Content]),
typeIt = Table.TransformColumnTypes(Source2, {"Date/Time", type datetime}),
#"Filtered Rows" = Table.SelectRows(typeIt, each ([#"Date/Time"] <> null)),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
// shift Info up one row for comparison
    shiftedList = List.RemoveFirstN(  #"Added Index"[Info],1),
    custom1 = Table.ToColumns(  #"Added Index") & {shiftedList},
    custom2 = Table.FromColumns(custom1,Table.ColumnNames(  #"Added Index") & {"NextInfo"}),
#"Added Custom" = Table.AddColumn(custom2, "NextLineStart", each if Text.Contains([Info],"start", Comparer.OrdinalIgnoreCase)
            and  Text.Contains([NextInfo],"start", Comparer.OrdinalIgnoreCase)
        then "delete" 
        else null),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([NextLineStart] = null)),
#"Added Custom3" = Table.AddColumn(#"Filtered Rows1", "Type", each if Text.Contains(Text.Lower([Info]),"start") then "start" else if Text.Contains(Text.Lower([Info]),"finished") then "finished" else null),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Info", "NextInfo", "NextLineStart"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each if [Type]="start" then [Index] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"no data!",Replacer.ReplaceValue,{"Type"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Replaced Value", {{"P7 [mbar]", null}}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Errors", List.Distinct(#"Replaced Errors"[Type]), "Type", "Date/Time"),
#"Added Custom2" = Table.AddColumn(#"Pivoted Column","Table",(i)=>Table.Sort(Table.SelectRows(Parameters, each [#"Date/Time"]>=i[start] and [#"Date/Time"]<=i[finished]),{{"Date/Time", Order.Ascending}}) , type table ),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Zeit/d", "D/T_5min/End", "Log_D/T-End - 1min", "P7 [mbar]", "P_5min / P_End", "Zeit nach Äquilibrierung (5.min) bis Ende [h]", "dp/dt_mittel [Pa/min]", "Custom", "start", "finished", "no data!"})
in
    #"Removed Columns2"

Segundo códigoes diferente, más rápido pero aún así no hace la tarea completamente:

 let

//Be sure to change the table names in the Source= and Source2= lines to be the actual table names from your workbook
    Source = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"P7 [mbar]", Int64.Type}}),
       
//get start/stop times table
    Source2 = Excel.CurrentWorkbook(){[Name="Log_Original"]}[Content],
    typeIt = Table.TransformColumnTypes(Source2, {"Date/Time", type datetime}),
    #"Filtered Rows" = Table.SelectRows(typeIt, each ([#"Date/Time"] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),

#"Added Custom" = Table.AddColumn(#"Added Index", "NextLineStart", each if Text.Contains([Info],"start", Comparer.OrdinalIgnoreCase) = true 
                and Text.Contains(#"Added Index"[Info]{[Index]+1},"start",Comparer.OrdinalIgnoreCase) = true 
            then "delete" 
            else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([NextLineStart] = null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Index", "NextLineStart"}),

//create a list of all the relevant start/stop times
     filterTimes = List.Combine(
                        List.Generate(
                            ()=> [times = List.DateTimes(#"Removed Columns1"[#"Date/Time"]{0}, 
                                                        Duration.TotalSeconds(#"Removed Columns1"[#"Date/Time"]{1}-#"Removed Columns1"[#"Date/Time"]{0})+1,
                                                        #duration(0,0,0,1)), IDX = 0],
                            each [IDX] < Table.RowCount(#"Removed Columns1"),
                            each [times = List.DateTimes(#"Removed Columns1"[#"Date/Time"]{[IDX]+2}, 
                                                        Duration.TotalSeconds(#"Removed Columns1"[#"Date/Time"]{[IDX]+3}-#"Removed Columns1"[#"Date/Time"]{[IDX]+2})+1,
                                                        #duration(0,0,0,1)), IDX = [IDX]+2],
                            each [times]
                                    )
                            ),

//filter the table using the list
filterTimesCol = Table.FromList(filterTimes,Splitter.SplitByNothing()),
filteredTable = Table.Join(#"Changed Type","Date/Time",filterTimesCol,"Column1",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(filteredTable,{"Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each DateTime.ToText([#"Date/Time"],"dd-MMM-yy")),
    #"Filtered Rows2" = Table.SelectRows(#"Added Custom1", each [#"Date/Time"] > #datetime(2019, 01, 01, 0, 0, 0)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"Date/Time", Order.Ascending}})
in
    #"Sorted Rows"

Por último, estoy cargando los datos de prueba en google.drive:

https://docs.google.com/spreadsheets/d/10AtLpQuRZriGKWz9vwAoVh7zp6kKKy5n/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

Respuesta1

Estoy seguro de que esto omitirá ciertas partes de sus necesidades, pero creo que el enfoque es sólido y debería llevarlo por el camino correcto.

En la mesa 1:

  • Agregue una columna para eliminar los segundos de la fecha y hora
  • Agregue una columna para mostrar solo la fecha
  • Otros cambios de formato, como cambios de nombre de columnas, reordenamiento, etc.

Se ve así al final:

ingrese la descripción de la imagen aquí

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"P7 [mbar]", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date/Time", "date_time"}, {"P7 [mbar]", "p7_mbar"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "date_time_no_seconds", each DateTime.From(DateTime.ToText([date_time],"yyyy-MM-dd HH:mm"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"date_time_no_seconds", type datetime}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"date_time", "date_time_no_seconds", "p7_mbar"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "date_time", "date_time - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"date_time - Copy", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"date_time - Copy", "date_of_date_time"}})
in
    #"Renamed Columns1"

En la mesa2:

  • Agregue una columna para eliminar los segundos de la fecha y hora
  • Agregue una columna para mostrar solo la fecha
  • Agregue una columna que contenga solo las palabras "Inicio", "Finalizado" y "Error" (lo hice usando "Columna personalizada de ejemplos", por lo que fue bastante fácil
  • Agregue una columna para enumerar solo los valores C1/C2
  • Crea un índice para cada fila dentro de cada fecha.
  • Crea un grupo de cada 2 filas dentro de cada fecha.
  • Fusionar el valor C1/C2 con el grupo creado anteriormente
  • Gire la columna de inicio/finalización/error usando la fecha y hora sin segundos sin agregación

Se ve así al final:

ingrese la descripción de la imagen aquí

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"Info", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date/Time", "date_time"}, {"Info", "info"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "date_time_no_seconds", each DateTime.From(DateTime.ToText([date_time],"yyyy-MM-dd HH:mm"))),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"date_time", "date_time_no_seconds", "info"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Reordered Columns",{{"date_time_no_seconds", type datetime}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type4", "date_time", "date_time - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"date_time - Copy", "date_of_date_time"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"date_of_date_time", type date}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type1", "Custom", each let splitinfo = Splitter.SplitTextByDelimiter(" C", QuoteStyle.None)([info]), splitsplitinfo1 = Splitter.SplitTextByDelimiter(".", QuoteStyle.None)(splitinfo{1}?) in Text.Combine({Text.Middle(Text.Proper(splitsplitinfo1{0}?), 2), Text.Reverse(Text.Middle(Text.Reverse(splitinfo{0}?), 9))}), type text),
    #"Inserted Kept Characters" = Table.AddColumn(#"Added Custom Column", "Kept Characters", each Text.Select([info], {"0".."9", "C"}), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Kept Characters",{{"Custom", "start_finish"}, {"Kept Characters", "leaktest_name"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"info"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"date_of_date_time"}, {{"Count", each _, type table [date_time=nullable datetime, date_of_date_time=nullable date, start_finish=text, leaktest_name=text]}}),
    Indexed = Table.TransformColumns(#"Grouped Rows", {{"Count", each Table.AddIndexColumn(_,"date_index", 1, 1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(Indexed, "Count", {"date_time", "date_time_no_seconds", "date_of_date_time", "start_finish", "leaktest_name", "date_index"}, {"date_time", "date_time_no_seconds", "date_of_date_time.1", "start_finish", "leaktest_name", "date_index"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Expanded Count",{{"date_time_no_seconds", type datetime}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type5",{"date_of_date_time.1"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "test_index", each Number.RoundUp([date_index]/2,0)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"test_index", Int64.Type}, {"date_index", Int64.Type}, {"leaktest_name", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type2", "Merged", each Text.Combine({[leaktest_name], "-", Text.From([test_index], "en-US")}), type text),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Merged Column",{"leaktest_name", "date_index", "test_index", "date_time"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[start_finish]), "start_finish", "date_time_no_seconds"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Pivoted Column",{{"Start", type datetime}, {"Finished", type datetime}, {"Failed", type datetime}})
in
    #"Changed Type3"

Finalmente, fusiona 1 y 2:

  • Fusionarse en la fecha
  • Agregue una columna para probar si la fecha y hora mbar sin segundos se encuentra entre las horas de inicio y finalización en esa fila (No he hecho nada con "Error", aunque es posible que quieras)
  • Filtre el conjunto de datos donde la prueba anterior devuelve verdadero
  • Retire la columna de filtro.
  • Robert es el hermano de tu madre

Se parece a esto:

ingrese la descripción de la imagen aquí

let
    Source = Table.NestedJoin(Table2, {"date_of_date_time"}, Table1, {"date_of_date_time"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"date_time_no_seconds", "p7_mbar"}, {"date_time_no_seconds", "p7_mbar"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "in_range", each [date_time_no_seconds] >= [Start] and [date_time_no_seconds] < [Finished]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([in_range] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"in_range"})
in
    #"Removed Columns"

información relacionada