Creeu taules amb diferents capçaleres a partir de diversos llibres

Formulació del problema

Tenim diversos fitxers (en el nostre exemple – 4 peces, en el cas general – tants com vulgueu) en una carpeta Informes:

A dins, aquests fitxers tenen aquest aspecte:

On:

• El full de dades que necessitem sempre es diu fotos, però pot estar a qualsevol part del llibre de treball.
  
• Més enllà del llençol fotos Cada llibre pot tenir altres fulls.
• Les taules amb dades tenen un nombre diferent de files i poden començar amb una fila diferent del full de treball.
• Els noms de les mateixes columnes en taules diferents poden ser diferents (per exemple, Quantitat = Quantitat = Quantitat).
• Les columnes de les taules es poden organitzar en un ordre diferent.

Tasca: recopilar dades de vendes de tots els fitxers del full fotos en una taula comuna per crear-ne posteriorment un resum o qualsevol altra anàlisi.

Pas 1. Preparació d'un directori de noms de columnes

El primer que cal fer és preparar un llibre de referència amb totes les opcions possibles per als noms de les columnes i la seva correcta interpretació:

Convertim aquesta llista en una taula "intel·ligent" dinàmica mitjançant el botó Format com a taula de la pestanya Home (Inici — Format com a taula) o drecera de teclat Ctrl+T i carregueu-lo a Power Query amb l'ordre Dades: de la taula/interval (Dades: de la taula/interval). En les darreres versions d'Excel, s'ha canviat de nom a Amb fulles (Del full).

A la finestra de l'editor de consultes de Power Query, tradicionalment suprimim el pas Tipus canviat i afegiu un pas nou en lloc d'ell fent clic al botó f_xa la barra de fórmules (si no és visible, podeu activar-la a la pestanya Crítica) i introduïu la fórmula allà amb el llenguatge Power Query integrat M:

=Taula.ToFiles(Font)

Aquesta ordre convertirà la carregada al pas anterior font taula de referència en una llista formada per llistes imbricades (Llista), cadascuna de les quals, al seu torn, és un parell de valors Va ser-es va convertir d'una línia:

Necessitarem aquest tipus de dades una mica més endavant, quan es canviï el nom de les capçaleres de totes les taules carregades.

Després de completar la conversió, seleccioneu les ordres Inici — Tancar i carregar — Tancar i carregar a... i tipus d'importació Només cal crear una connexió (Inici — Tanca i càrrega — Tanca i càrrega a... — Només crea connexió) i torneu a Excel.

Pas 2. Carreguem tot de tots els fitxers tal qual

Ara carreguem el contingut de tots els nostres fitxers des de la carpeta, de moment, tal com està. Selecció d'equips Dades – Obtenir dades – Del fitxer – Des de la carpeta (Dades — Obtenir dades — Del fitxer — Des de la carpeta) i després la carpeta on es troben els nostres llibres font.

A la finestra de vista prèvia, feu clic Converteix (Transformar) or Canviar (Edita):

A continuació, amplieu el contingut de tots els fitxers descarregats (binari) botó amb fletxes dobles a l'encapçalament de la columna Estoig:

Power Query a l'exemple del primer fitxer (Vostok.xlsx) ens demanarà el nom del full que volem treure de cada quadern – tria fotos i premeu D'acord:

Després d'això (de fet), es produiran diversos esdeveniments que no són evidents per a l'usuari, les conseqüències dels quals són clarament visibles al tauler esquerre:

1. Power Query agafarà el primer fitxer de la carpeta (el tindrem Vostok.xlsx — veure Exemple de fitxer) com a exemple i importa el seu contingut mitjançant la creació d'una consulta Converteix un fitxer de mostra. Aquesta consulta tindrà alguns passos senzills com ara font (accés al fitxer) navegació (selecció de fulls) i possiblement aixecant els títols. Aquesta sol·licitud només pot carregar dades d'un fitxer específic Vostok.xlsx.
   
2. A partir d'aquesta sol·licitud, es crearà la funció associada Converteix fitxer (indicat per una icona característica f_x), on el fitxer font ja no serà una constant, sinó un valor variable, un paràmetre. Així, aquesta funció pot extreure dades de qualsevol llibre que hi introduïm com a argument.
   
3. La funció s'aplicarà al seu torn a cada fitxer (binari) de la columna Estoig - Step és el responsable d'això Truqueu a la funció personalitzada a la nostra consulta que afegeix una columna a la llista de fitxers Converteix fitxer amb els resultats d'importació de cada llibre de treball:

   

4. S'eliminen les columnes addicionals.
5. El contingut de les taules imbricades s'amplia (pas Columna de taula ampliada) - i veiem els resultats finals de la recollida de dades de tots els llibres:

   
   

Pas 3. Poliment

La captura de pantalla anterior mostra clarament que el muntatge directe "tal com està" va resultar ser de mala qualitat:

• Les columnes estan invertides.
• Moltes línies extra (buides i no només).
• Les capçaleres de les taules no es perceben com a capçaleres i es barregen amb dades.
  

Podeu solucionar tots aquests problemes molt fàcilment; només cal que modifiqueu la consulta Converteix fitxer de mostra. Tots els ajustaments que li fem passaran automàticament a la funció Converteix fitxer associada, la qual cosa significa que s'utilitzaran més endavant quan s'importin dades de cada fitxer.

En obrir una petició Converteix un fitxer de mostra, afegiu passos per filtrar les files innecessàries (per exemple, per columna Column2) i pujant els encapçalaments amb el botó Utilitzeu la primera línia com a capçaleres (Utilitzeu la primera fila com a capçaleres). La taula es veurà molt millor.

Per tal que les columnes de diferents fitxers encaixin automàticament les unes sota les altres més endavant, s'han de posar el mateix nom. Podeu realitzar aquest canvi de nom massiu segons un directori creat prèviament amb una línia de codi M. Tornem a prémer el botó f_x a la barra de fórmules i afegiu una funció per canviar:

= Table.RenameColumns(#"Capçaleres elevades", Capçaleres, MissingField.Ignore)

Aquesta funció pren la taula del pas anterior Capçaleres elevades i canvia el nom de totes les columnes d'acord amb la llista de cerca imbricada Titulars. Tercer argument MissingField.Ignora és necessari perquè en aquells encapçalaments que es troben al directori, però no a la taula, no es produeixi cap error.

De fet, això és tot.

Tornant a la petició Informes veurem una imatge completament diferent, molt més bonica que l'anterior:

• Què és Power Query, Power Pivot, Power BI i per què els necessita un usuari d'Excel
  
• Recollida de dades de tots els fitxers d'una carpeta determinada
• Recollida de dades de tots els fulls del llibre en una taula