Byg tabeller med forskellige overskrifter fra flere bøger

Formulering af problemet

Vi har flere filer (i vores eksempel - 4 stykker, i det generelle tilfælde - så mange du vil) i en mappe Rapporter:

Indeni ser disse filer sådan ud:

Hvori:

• Det datablad, vi skal bruge, kaldes altid pics, men kan være hvor som helst i projektmappen.
  
• Ud over arket pics Hver bog kan have andre ark.
• Tabeller med data har et andet antal rækker og kan starte med en anden række på regnearket.
• Navnene på de samme kolonner i forskellige tabeller kan være forskellige (f.eks. Mængde = Mængde = Antal).
• Kolonner i tabeller kan arrangeres i en anden rækkefølge.

Opgave: Indsaml salgsdata fra alle filer fra arket pics i én fælles tabel for efterfølgende at bygge et resumé eller anden analyse på den.

Trin 1. Forberedelse af en mappe med kolonnenavne

Den første ting at gøre er at udarbejde en opslagsbog med alle mulige muligheder for kolonnenavne og deres korrekte fortolkning:

Vi konverterer denne liste til en dynamisk "smart" tabel ved at bruge knappen Formater som tabel på fanen Home (Hjem — Formater som tabel) eller tastaturgenvej Ctrl+T og indlæs den i Power Query med kommandoen Data – Fra tabel/område (Data — fra tabel/område). I de seneste versioner af Excel er det blevet omdøbt til Med blade (Fra ark).

I vinduet Power Query-forespørgselseditor sletter vi traditionelt trinnet Ændret Type og tilføje et nyt trin i stedet for det ved at klikke på knappen f_xi formellinjen (hvis den ikke er synlig, kan du aktivere den på fanen anmeldelse) og indtast formlen der i det indbyggede Power Query-sprog M:

=Tabel.ToRows(Kilde)

Denne kommando vil konvertere den, der blev indlæst i det forrige trin Kilde referencetabel til en liste bestående af indlejrede lister (List), som hver igen er et par værdier Det var-blev fra en linje:

Vi får brug for denne type data lidt senere, når vi masseomdøber headere fra alle indlæste tabeller.

Når konverteringen er fuldført, skal du vælge kommandoerne Hjem — Luk og indlæs — Luk og indlæs... og importtype Bare opret en forbindelse (Hjem — Luk&indlæs — Luk&indlæs til... — Opret kun forbindelse) og gå tilbage til Excel.

Trin 2. Vi indlæser alt fra alle filer, som det er

Lad os nu indlæse indholdet af alle vores filer fra mappen – indtil videre, som det er. Valg af hold Data – Hent data – Fra fil – Fra mappe (Data — Hent data — Fra fil — Fra mappe) og så mappen, hvor vores kildebøger er.

Klik på i forhåndsvisningsvinduet Konverter (Transformer) or Skift (Edit):

Og udvid derefter indholdet af alle downloadede filer (Binær) knap med dobbeltpile i kolonneoverskriften Indhold:

Power Query på eksemplet med den første fil (Vostok.xlsx) vil spørge os om navnet på det ark, vi vil tage fra hver projektmappe – vælg pics og tryk på OK:

Derefter vil der (faktisk) forekomme flere hændelser, der ikke er indlysende for brugeren, hvis konsekvenser er tydeligt synlige i venstre panel:

1. Power Query tager den første fil fra mappen (vi har den Vostok.xlsx — se Eksempel på fil) som et eksempel og importerer dets indhold ved at oprette en forespørgsel Konverter eksempelfil. Denne forespørgsel vil have nogle enkle trin som Kilde (filadgang) Navigation (arkvalg) og eventuelt hæve titlerne. Denne anmodning kan kun indlæse data fra én bestemt fil Vostok.xlsx.
   
2. Baseret på denne anmodning vil den funktion, der er knyttet til den, blive oprettet Konverter fil (angivet med et karakteristisk ikon f_x), hvor kildefilen ikke længere vil være en konstant, men en variabel værdi – en parameter. Således kan denne funktion udtrække data fra enhver bog, som vi smutter ind i det som et argument.
   
3. Funktionen vil blive anvendt på skift til hver fil (binær) fra kolonnen Indhold – step er ansvarlig for dette Kald brugerdefineret funktion i vores forespørgsel, der tilføjer en kolonne til listen over filer Konverter fil med import af resultater fra hver projektmappe:

   

4. Ekstra kolonner fjernes.
5. Indholdet af indlejrede tabeller udvides (trin Udvidet tabelkolonne) – og vi ser de endelige resultater af dataindsamling fra alle bøger:

   
   

Trin 3. Slibning

Det forrige skærmbillede viser tydeligt, at den direkte samling "som den er" viste sig at være af dårlig kvalitet:

• Søjlerne er omvendt.
• Mange ekstra linjer (tomme og ikke kun).
• Tabeloverskrifter opfattes ikke som overskrifter og er blandet med data.
  

Du kan løse alle disse problemer meget nemt - bare finjuster forespørgslen Konverter prøvefil. Alle justeringer, som vi foretager til det, vil automatisk falde ind i den tilknyttede Konverter fil-funktion, hvilket betyder, at de vil blive brugt senere ved import af data fra hver fil.

Ved at åbne en anmodning Konverter eksempelfil, tilføje trin for at filtrere unødvendige rækker (f.eks. efter kolonne Column2) og hæve overskrifterne med knappen Brug første linje som overskrifter (Brug første række som overskrifter). Bordet vil se meget bedre ud.

For at kolonner fra forskellige filer automatisk skal passe ind under hinanden senere, skal de have samme navn. Du kan udføre en sådan masseomdøbning i henhold til en tidligere oprettet mappe med en linje M-kode. Lad os trykke på knappen igen f_x i formellinjen og tilføj en funktion for at ændre:

= Table.RenameColumns(#”Elevated Headers”, Headers, MissingField.Ignore)

Denne funktion tager tabellen fra det forrige trin Forhøjede overskrifter og omdøber alle kolonner i den i henhold til den indlejrede opslagsliste Overskrifter. Tredje argument MissingField.Ignorer er nødvendig, så der ikke opstår en fejl på de overskrifter, der er i mappen, men ikke i tabellen.

Faktisk er det alt.

Vender tilbage til anmodningen Rapporter vi vil se et helt andet billede – meget pænere end det forrige:

• Hvad er Power Query, Power Pivot, Power BI og hvorfor en Excel-bruger har brug for dem
  
• Indsamling af data fra alle filer i en given mappe
• Indsamling af data fra alle ark i bogen i én tabel