Constrúe táboas con diferentes cabeceiras a partir de varios libros

Formulación do problema

Temos varios ficheiros (no noso exemplo – 4 pezas, no caso xeral – tantos como queiras) nun cartafol Informes:

Dentro, estes ficheiros teñen o seguinte aspecto:

Onde:

• A ficha técnica que necesitamos sempre se chama fotos, pero pode estar en calquera lugar do libro de traballo.
  
• Máis aló da folla fotos Cada libro pode ter outras follas.
• As táboas con datos teñen un número diferente de filas e poden comezar cunha fila diferente na folla de traballo.
• Os nomes das mesmas columnas en táboas diferentes poden diferir (por exemplo, Cantidade = Cantidade = Cantidade).
• As columnas das táboas pódense organizar nunha orde diferente.

Tarefa: recolle datos de vendas de todos os ficheiros da folla fotos nunha táboa común para crear posteriormente un resumo ou calquera outra análise sobre ela.

Paso 1. Preparación dun directorio de nomes de columnas

O primeiro que hai que facer é preparar un libro de consulta con todas as opcións posibles para os nomes das columnas e a súa correcta interpretación:

Convertemos esta lista nunha táboa "intelixente" dinámica usando o botón Formatar como táboa da pestana casa (Inicio — Formato como táboa) ou atallo de teclado Ctrl+T e cárgueo en Power Query co comando Datos: da táboa/rango (Datos: da táboa/rango). Nas versións recentes de Excel, cambiou o nome a Con follas (Da folla).

Na xanela do editor de consultas de Power Query, tradicionalmente eliminamos o paso Tipo cambiado e engade un novo paso no canto del facendo clic no botón f_xna barra de fórmulas (se non está visible, pode activalo na pestana revisión) e introduza alí a fórmula na linguaxe de Power Query incorporada M:

=Táboa.ParaFichas(Fonte)

Este comando converterá o cargado no paso anterior fonte táboa de referencia nunha lista formada por listas aniñadas (Lista), cada unha das cales, á súa vez, é un par de valores Foi-volveuse dunha liña:

Necesitaremos este tipo de datos un pouco máis tarde, cando se renomeen en masa as cabeceiras de todas as táboas cargadas.

Despois de completar a conversión, seleccione os comandos Inicio — Pechar e cargar — Pechar e cargar en… e tipo de importación Só ten que crear unha conexión (Inicio — Pechar e cargar — Pechar e cargar en… — Só crear conexión) e volve a Excel.

Paso 2. Cargamos todo de todos os ficheiros tal e como están

Agora imos cargar o contido de todos os nosos ficheiros desde o cartafol, polo de agora, tal e como está. Selección de equipos Datos – Obter datos – Do ficheiro – Do cartafol (Datos — Obter datos — Do ficheiro — Do cartafol) e despois o cartafol onde están os nosos libros fonte.

Na xanela de vista previa, fai clic Converter (Transformar) or Cambiar (Editar):

E despois expande o contido de todos os ficheiros descargados (Binario) botón con dobres frechas no título da columna contido:

Power Query sobre o exemplo do primeiro ficheiro (Vostok.xlsx) preguntaranos o nome da folla que queremos sacar de cada caderno de traballo – elixe fotos e prema Aceptar:

Despois diso (de feito), ocorrerán varios eventos que non son obvios para o usuario, cuxas consecuencias son claramente visibles no panel esquerdo:

1. Power Query tomará o primeiro ficheiro do cartafol (terémolo Vostok.xlsx — Ver Exemplo de ficheiro) como exemplo e importa o seu contido creando unha consulta Converter ficheiro de mostra. Esta consulta terá algúns pasos sinxelos como fonte (acceso ao ficheiro) navegación (selección de follas) e posiblemente levantando os títulos. Esta solicitude só pode cargar datos dun ficheiro específico Vostok.xlsx.
   
2. En base a esta solicitude, crearase a función asociada a ela Converter ficheiro (indicado por unha icona característica f_x), onde o ficheiro fonte xa non será unha constante, senón un valor variable, un parámetro. Así, esta función pode extraer datos de calquera libro que lle introduzamos como argumento.
   
3. A función aplicarase á súa vez a cada ficheiro (Binario) da columna contido – Step é o responsable diso Chamar a función personalizada na nosa consulta que engade unha columna á lista de ficheiros Converter ficheiro cos resultados de importación de cada libro de traballo:

   

4. Elimínanse as columnas adicionais.
5. Amplíase o contido das táboas aniñadas (paso Columna da táboa ampliada) - e vemos os resultados finais da recollida de datos de todos os libros:

   
   

Paso 3. Lixado

A captura de pantalla anterior mostra claramente que a montaxe directa "tal como está" resultou ser de mala calidade:

• As columnas están invertidas.
• Moitas liñas extra (baleiras e non só).
• As cabeceiras das táboas non se perciben como cabeceiras e mestúranse cos datos.
  

Podes solucionar todos estes problemas moi facilmente: só tes que modificar a consulta Converter ficheiro de mostra. Todos os axustes que lle fagamos caerán automaticamente na función Converter ficheiro asociada, o que significa que se utilizarán máis tarde ao importar datos de cada ficheiro.

Ao abrir unha solicitude Converter ficheiro de mostra, engade pasos para filtrar filas innecesarias (por exemplo, por columna Column2) e subindo os títulos co botón Use a primeira liña como cabeceiras (Utiliza a primeira fila como cabeceiras). A mesa quedará moito mellor.

Para que as columnas de diferentes ficheiros encaixan automaticamente unhas debaixo das outras máis adiante, débense nomear igual. Pode realizar ese cambio de nome masivo segundo un directorio creado previamente cunha liña de código M. Prememos de novo o botón f_x na barra de fórmulas e engade unha función para cambiar:

= Table.RenameColumns(#"Encabezados elevados", Cabeceiras, MissingField.Ignorar)

Esta función toma a táboa do paso anterior Cabeceiras elevadas e renomea todas as columnas nel segundo a lista de busca aniñada Titulares. Terceiro argumento MissingField.Ignorar é necesario para que naquelas cabeceiras que están no directorio, pero non están na táboa, non se produza un erro.

En realidade, iso é todo.

Volvendo á solicitude Informes veremos unha imaxe completamente diferente, moito máis bonita que a anterior:

• Que é Power Query, Power Pivot, Power BI e por que un usuario de Excel os necesita
  
• Recopilación de datos de todos os ficheiros dun cartafol determinado
• Recopilación de datos de todas as follas do libro nunha soa táboa