Power Query: obtenga datos cuando cambien los nombres de la hoja/tabla (2 formas)

Recientemente, uno de los miembros de nuestra academia preguntó cómo obtener datos de un libro de Excel cuando cambian los nombres de las hojas o tablas. De forma predeterminada, Power Query usa nombres para filtrar los datos correctos. Por lo tanto, si los nombres de la hoja o de la tabla cambian cada vez que se descarga un informe, se producirán errores.

No queremos actualizar la consulta y no queremos editar el libro. Entonces, en lugar de obtener una tabla u hoja por nombre, ¿cómo podemos obtenerla por posición? Ese es el problema que estamos resolviendo en esta publicación.

Tabla de contenido

• Comprender el paso de navegación
• La solución
• Método de interfaz de usuario
• Escribir código M
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0140 PQ – Cambio de nombre de hoja o tabla

Tener acceso

Mira el vídeo en YouTube

Comprender el paso de navegación

Comencemos por comprender qué hace Power Query de forma predeterminada.

Para obtener una tabla u hoja en Power Query, hacemos clic en Datos Obtener datos del archivo del libro de Excel .

A continuación, navegamos hasta el libro de Excel y luego hacemos clic en Importar .

En la ventana del Navegador , seleccionamos la hoja o tabla individual y hacemos clic en Transformar datos .

Esto crea el paso de navegación en la ventana Pasos aplicados . Dependiendo de su configuración, es posible que también tenga encabezados promocionados y/o pasos de tipo modificado .

Como nota al margen, Navegación no es en realidad el nombre del paso. Mirar el Editor avanzado revela el nombre real del paso. En nuestro ejemplo, el paso se llama May_08_Table .

Habíamos seleccionado la hoja denominada 08052023 ; el paso se llamaría 08052023_Sheet en el editor avanzado.

Ya sea que miremos el código en el Editor avanzado o en la Barra de fórmulas , podemos ver la siguiente fórmula para el paso de Navegación.

= Source{[Item="May_08",Kind="Table"]}[Data]

¿Qué hace este código?

• Fuente : el nombre del paso anterior.
• {[Nombre=”miTabla”, Tipo=”Tabla”]}
  • Esto filtra el registro por Nombre y Tipo.
  • Los filtros siempre deben dejar un único registro restante para que este método funcione.
• [Datos] : este es el nombre de la columna que se expandirá.

Si solo filtramos por Tipo , esto puede generar un error donde hay varias Tablas u Hojas. En nuestro ejemplo, el código siguiente crea un error porque hay tres tablas en el libro.

= Source{[Kind="Table"]}[Data]

La solución

Entonces, para filtrar una tabla u hoja específica por posición, necesitamos encontrar un enfoque alternativo que logre el mismo resultado.

Veamos esto de dos maneras (1) Interfaz de usuario (2) Escritura de código M

Método de interfaz de usuario

Si solo queremos utilizar la interfaz de usuario, podemos aplicar los siguientes pasos.

1. Elimine el paso de navegación (elimine también los encabezados promocionados y el tipo modificado si se aplicaron automáticamente).
2. Filtre la columna Tipo a Hoja o Tabla para su escenario.
3. Seleccione la columna Datos y luego haga clic en Inicio Quitar columnas (menú desplegable) Quitar otras columnas
4. Haga clic en la palabra Tabla en la columna Datos para la posición en la que queremos profundizar.

Eso es todo. Hemos terminado.

Echemos un vistazo al código M en el Editor avanzado.

let    Source = Excel.Workbook(File.Contents("C:ExamplesExcel File.xlsx"), null, true),    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),    Data = #"Removed Other Columns"{2}[Data]in    Data

Perfecto. No se incluyen nombres específicos de tablas o hojas en el código; sólo el número de fila de la hoja o tabla. Entonces, si los nombres cambian, no es un problema; Todavía funciona correctamente.

La pieza clave del código es {2} . Declara que queremos la tercera Mesa. Si lo reemplazamos con {0}, sería la primera Tabla, la segunda Tabla sería {1}, etc.

Ahora quizás te preguntes en qué orden aparecen los objetos de la hoja de trabajo. Power Query enumera las hojas en el mismo orden que el libro. Primero, Hojas de izquierda a derecha, luego Tablas de izquierda a derecha. Por lo tanto, siempre que filtremos por Tipo inicialmente, deberíamos poder obtener la enésima hoja o tabla de un libro de trabajo.

El código muestra 3 pasos para lograr lo que hizo Navigation en 1. Esto no es un gran problema; el código sigue siendo eficiente y se ejecutará rápidamente. Pero si desea que su código M sea lo más corto posible, la versión del código M que aparece a continuación puede ser una mejor opción.

Escribir código M

Si estamos dispuestos a aventurarnos a escribir código M, podemos combinar el código de las secciones anteriores en una sola línea. Sólo necesitamos 3 elementos.

• Filtrar la tabla con la función Table.SelectRows()
• Consigue la enésima posición de la tabla. {2} en nuestro ejemplo
• Profundice en la columna [Datos]

Para lograrlo, elimine todos los pasos excepto la Fuente. Luego, haga clic en el ícono fx al lado de la barra de fórmulas para crear un nuevo paso. Introduzca la siguiente:

= Table.SelectRows(Source, each ([Kind] = "Table")){2}[Data]

Si observa detenidamente, notará que se trata de una combinación de otras secciones de código creadas por Power Query para el método de interfaz de usuario anterior.

Ahora nos beneficiamos de un solo paso para seleccionar una Tabla u hoja por posición.

Conclusión

En esta publicación hemos visto cómo filtrar por posición en Power Query para extraer una tabla u hoja cuando cambian sus nombres.

Las técnicas no se limitan a este escenario, sino que son útiles para muchas otras situaciones complicadas de PQ.

Artículos Relacionados :

• Cambiar el nombre de las columnas en Power Query cuando cambian los nombres
• Cómo eliminar espacios en Power Query
• Introducción a Power Query

---