Tiyerekeze kuti tili ndi fayilo ya Excel yokhala ndi matebulo angapo anzeru:
Ngati muyika matebulo awa mu Power Query m'njira yokhazikika pogwiritsa ntchito lamulo Deta - Pezani zambiri - Kuchokera pafayilo - Kuchokera m'buku (Deta - Pezani Zambiri - Kuchokera pafayilo - Kuchokera ku Buku la Ntchito), ndiye timapeza zinthu monga izi:
Chithunzicho, ndikuganiza, ndichodziwika kwa ogwiritsa ntchito ambiri a Power Query. Matebulo ofananirako amatha kuwoneka mutaphatikiza mafunso (a la VLOOKUP), magulu (kulamula Gulu ndi tsamba Transformation), kulowetsa mafayilo onse kuchokera pafoda yopatsidwa, ndi zina.
Chotsatira chomveka bwino muzochitika izi nthawi zambiri chimakhala kukulitsa matebulo onse okhala nthawi imodzi - pogwiritsa ntchito batani lokhala ndi mivi iwiri pamutu wagawo. Deta:
Zotsatira zake, timapeza mizere yonse kuchokera kumatebulo onse kukhala imodzi. Chilichonse ndichabwino, chosavuta komanso chomveka.
Tsopano yerekezani kuti gawo latsopano (Kuchotsera) linawonjezedwa m'matebulo oyambira ndi/kapena imodzi mwazomwe zilipo (City) idachotsedwa:
Kenako pempho lathu pambuyo pakusintha lidzabweretsa chithunzi chosakongola kwambiri - kuchotsera sikunawonekere, ndipo gawo la mzindawo lidakhala lopanda kanthu, koma silinathe:
Ndipo ndizosavuta kuwona chifukwa chake - mu bar ya formula mutha kuwona bwino lomwe kuti mayina amizere yowonjezedwa ndi hardcoded pazokangana zantchito. Table.ExpandTableColumn monga mndandanda m'mabulaketi opotanata.
Kuzungulira vutoli ndikosavuta. Choyamba, tiyeni titenge mayina amzawo kuchokera pamutu wa tebulo lililonse (mwachitsanzo, loyamba) pogwiritsa ntchito ntchitoyi Table.ColumnNames. Zidzawoneka ngati:
Pano:
- #"Zigawo zina zachotsedwa" - dzina la sitepe yapitayi, kumene timatengera deta
- 0 {} - chiwerengero cha tebulo limene timachotsamo mutu (kuwerengera kuchokera ku ziro, mwachitsanzo 0 ndi tebulo loyamba)
- [Zambiri] - dzina la mzati mu sitepe yapitayi, kumene matebulo okulitsidwa ali
Zimatsalira kuti zilowe m'malo mwa zomangamanga zomwe zapezeka mu bar ya formula mu ntchitoyi Table.ExpandTableColumn pa sitepe ya kukulitsa matebulo m'malo mwa ndandanda zolimba. Zonsezi ziyenera kuwoneka motere:
Ndizomwezo. Ndipo sipadzakhalanso zovuta pakukulitsa matebulo okhala ndi zisa pomwe magwero akusintha.
- Kupanga matebulo amitundu yambiri kuchokera papepala limodzi mu Power Query
- Pangani matebulo okhala ndi mitu yosiyanasiyana kuchokera pamafayilo angapo a Excel
- Kusonkhanitsa deta kuchokera pamasamba onse a bukhulo mu tebulo limodzi