Ibni tabelli b'intestaturi differenti minn kotba multipli

Formulazzjoni tal-problema

Għandna diversi fajls (fl-eżempju tagħna - 4 biċċiet, fil-każ ġenerali - kemm tixtieq) f'folder wieħed Rapporti:

Ġewwa, dawn il-fajls jidhru bħal dan:

Fejn:

• Il-folja tad-dejta li għandna bżonn tissejjaħ dejjem PICS, iżda jista 'jkun kullimkien fil-ktieb tax-xogħol.
  
• Lil hinn mill-folja PICS Kull ktieb jista’ jkollu folji oħra.
• It-tabelli bid-dejta għandhom numru differenti ta’ ringieli u jistgħu jibdew b’ringiela differenti fuq il-worksheet.
• L-ismijiet tal-istess kolonni f'tabelli differenti jistgħu jvarjaw (per eżempju, Kwantità = Kwantità = Qty).
• Kolonni fit-tabelli jistgħu jiġu rranġati f'ordni differenti.

Kompitu: iġbor data tal-bejgħ mill-fajls kollha mill-folja PICS f'tabella komuni waħda sabiex sussegwentement tinbena sommarju jew kwalunkwe analitika oħra fuqha.

Pass 1. Tħejjija ta 'direttorju ta' ismijiet ta 'kolonni

L-ewwel ħaġa li għandek tagħmel hi li tipprepara ktieb ta’ referenza bl-għażliet kollha possibbli għall-ismijiet tal-kolonni u l-interpretazzjoni korretta tagħhom:

Aħna nikkonverti din il-lista f'tabella dinamika "intelliġenti" billi tuża l-buttuna Format bħala tabella fuq it-tab home (Id-dar — Format bħala Tabella) jew shortcut tat-tastiera Ctrl+T u tagħbijaha f'Power Query bil-kmand Dejta – Minn Tabella/Firxa (Data — Minn Tabella/Medda). F'verżjonijiet reċenti ta 'Excel, ingħata isem ġdid għal Bil-weraq (Mill-folja).

Fit-tieqa tal-editur tal-mistoqsijiet tal-Power Query, aħna tradizzjonalment inħassru l-pass Tip Mibdul u żid pass ġdid minfloku billi tikklikkja fuq il-buttuna f_xfil-bar tal-formula (jekk ma tkunx viżibbli, allura tista 'tippermettiha fuq it-tab reviżjoni) u daħħal il-formula hemmhekk fil-lingwa integrata tal-Power Query M:

=Tabella.ToRows(Sors)

Dan il-kmand se jikkonverti dak mgħobbi fil-pass preċedenti sors tabella ta’ referenza f’lista li tikkonsisti f’listi mdaħħla (Lista), li kull waħda minnhom, min-naħa tagħha, hija par ta’ valuri Kien-sar minn linja waħda:

Ikollna bżonn dan it-tip ta 'dejta ftit aktar tard, meta l-isem mill-ġdid tal-headers tal-massa mit-tabelli kollha mgħobbija.

Wara li tlesti l-konverżjoni, agħżel il-kmandi Id-dar — Agħlaq u Tagħbija — Agħlaq u Tagħbija fi... u t-tip ta' importazzjoni Oħloq biss konnessjoni (Dar — Agħlaq&Tagħbija — Agħlaq&Tagħbija għal... — Oħloq biss konnessjoni) u mur lura għal Excel.

Pass 2. Aħna tagħbija kollox mill-fajls kollha kif inhu

Issa ejja tagħbija l-kontenut tal-fajls kollha tagħna mill-folder - għalissa, kif inhu. Għażla ta' timijiet Dejta – Ikseb data – Mill-fajl – Mill-folder (Data — Ikseb id-Data — Mill-fajl — Mill-folder) u mbagħad il-folder fejn il-kotba tas-sors tagħna huma.

Fit-tieqa tal-preview, ikklikkja Ikkonverti (Trasforma) or Bidla (Edit):

U mbagħad jespandu l-kontenut tal-fajls kollha mniżżla (Binarju) buttuna bi vleġeġ doppji fl-intestatura tal-kolonna kontenut:

Power Query fuq l-eżempju tal-ewwel fajl (Vostok.xlsx) se jistaqsina l-isem tal-folja li rridu nieħdu minn kull ktieb tax-xogħol - agħżel PICS u agħfas OK:

Wara dan (fil-fatt), se jseħħu diversi avvenimenti li mhumiex ovvji għall-utent, li l-konsegwenzi tagħhom huma viżibbli b'mod ċar fil-pannell tax-xellug:

1. Power Query se tieħu l-ewwel fajl mill-folder (se jkollna dan Vostok.xlsx — ara Eżempju tal-fajl) bħala eżempju u timporta l-kontenut tagħha billi toħloq mistoqsija Ikkonverti fajl tal-kampjun. Din il-mistoqsija se jkollha xi passi sempliċi bħal sors (aċċess għall-fajl) Navigazzjoni (għażla tal-folji) u possibbilment jgħollu t-titoli. Din it-talba tista' biss tagħbija data minn fajl wieħed speċifiku Vostok.xlsx.
   
2. Ibbażat fuq din it-talba, il-funzjoni assoċjata magħha se tinħoloq Ikkonverti l-fajl (indikat b'ikona karatteristika f_x), fejn il-fajl tas-sors ma jibqax kostanti, iżda valur varjabbli – parametru. Għalhekk, din il-funzjoni tista 'tiġbed data minn kwalunkwe ktieb li aħna slip fih bħala argument.
   
3. Il-funzjoni se tiġi applikata mbagħad għal kull fajl (Binarju) mill-kolonna kontenut – pass huwa responsabbli għal dan Sejħa funzjoni tad-dwana fil-mistoqsija tagħna li żżid kolonna mal-lista ta 'fajls Ikkonverti l-fajl bir-riżultati tal-importazzjoni minn kull ktieb tax-xogħol:

   

4. Kolonni żejda jitneħħew.
5. Il-kontenut tat-tabelli nested huma estiżi (pass Kolonna tat-tabella estiża) – u naraw ir-riżultati finali tal-ġbir tad-dejta mill-kotba kollha:

   
   

Pass 3. Xkatlar

Il-screenshot preċedenti turi biċ-ċar li l-assemblaġġ dirett "kif inhu" irriżulta li kien ta 'kwalità fqira:

• Il-kolonni huma maqlubin.
• Ħafna linji żejda (vojta u mhux biss).
• L-intestaturi tat-tabella mhumiex pperċepiti bħala headers u huma mħallta mad-dejta.
  

Tista' tiffissa dawn il-problemi kollha faċilment - sempliċement tweak il-mistoqsija Ikkonverti l-Fajl tal-Kampjun. L-aġġustamenti kollha li nagħmlu għaliha awtomatikament jaqgħu fil-funzjoni assoċjata tal-fajl Ikkonverti, li jfisser li se jintużaw aktar tard meta timporta data minn kull fajl.

Billi tiftaħ talba Ikkonverti fajl tal-kampjun, żid passi biex tiffiltra ringieli mhux meħtieġa (per eżempju, bil-kolonna Column2) u tgħolli l-intestaturi bil-buttuna Uża l-ewwel linja bħala headers (Uża l-ewwel ringiela bħala headers). It-tabella tidher ħafna aħjar.

Sabiex kolonni minn fajls differenti jidħlu awtomatikament taħt xulxin aktar tard, għandhom jissemmew l-istess. Tista 'twettaq tali isem mill-ġdid tal-massa skont direttorju maħluq qabel b'linja waħda ta' M-code. Ejja nagħfas il-buttuna mill-ġdid f_x fil-bar tal-formula u żid funzjoni biex tbiddel:

= Tabella.RenameColumns(#”Intestaturi elevati”, Intestaturi, MissingField.Injora)

Din il-funzjoni tieħu t-tabella mill-pass preċedenti Ras elevati u tibdel l-isem tal-kolonni kollha fiha skont il-lista ta’ tiftix imniżżla Punti Ewlenin. It-tielet argument MissingField.Injora hija meħtieġa sabiex fuq dawk l-intestaturi li huma fid-direttorju, iżda li mhumiex fit-tabella, ma jseħħx żball.

Fil-fatt, dak kollu.

Nirritornaw għat-talba Rapporti se naraw stampa kompletament differenti - ħafna isbaħ minn dik ta 'qabel:

• X'inhu Power Query, Power Pivot, Power BI u għaliex utent ta' Excel jeħtieġhom
  
• Il-ġbir tad-dejta mill-fajls kollha f'folder partikolari
• Il-ġbir tad-dejta mill-folji kollha tal-ktieb f'tabella waħda