- I-Power Query ikuvumela ukuthi uhlanganise amafayela amaningi kusuka kufolda yendawo noma yamafu uma nje abelana nge-schema yedatha efanayo.
- Ukusebenzisa ifayela lesampula kwenza kube lula ukusebenzisa ukuguqulwa kwenqwaba, njengokususa imigqa ephezulu noma ukushintsha ama-header.
- Kunemikhawulo lapho amakholomu engahambisani ncamashi, okudinga ukuhlanzwa ngesandla noma ukusetshenziswa kwezikripthi ze-VBA ezimweni eziyinkimbinkimbi.
Cabanga nje ukuthi unemibiko eminingi yanyanga zonke noma yamasonto onke esakazeke kumafolda, futhi esikhundleni sokuchitha isikhathi sakho sasekuseni ukopisha futhi unamathisela idatha, ungahlanganisa yonke into ibe yispredishithi esisodwa se-Excel ngokuchofoza okumbalwa nje. Yilapho i-Power Query ingena khona, ithuluzi eliyisandla sokudla kunoma ubani osebenza ngedatha, elisivumela ukuthi sihlanganise amafayela abelana ngesakhiwo esifanayo kalula.
Into emnandi kakhulu ngalolu hlelo ukuthi alukukhawuleli kufolda yendawo ku-PC yakho; ungadonsa idatha kumasayithi e-SharePoint, i-Azure Data Lake, noma ama-Azure blobs . Isihluthulelo sokwenza lokhu kusebenze kahle ukuthi amafayela ane-schema efanayo, okusho ukuthi amakholomu ayahambisana, yize i-Power Query ihlakaniphe ngokwanele ukusiza ekuhlanzeni idatha uma iqukethe umsindo noma imigqa engadingekile.
Inqubo yokuhlanganisa isinyathelo ngesinyathelo
Uma uxhuma emthonjeni wedatha, into yokuqala ozoyibona ukubuka kwesistimu yamafayela. Lapha, ikholomu ebaluleke kakhulu yi -Content, equkethe ukumelwa kwe-binary kwedokhumenti ngayinye. Uma uchofoza kwenye yalezi zindinganiso ze-binary, uhlelo luzozama ukuthola ukuthi ingabe ifayela le-CSV, i-JSON, noma i-Excel ukuze ulivule. Ukuze uhlanganise lonke iqembu, unezinketho ezimbili: chofoza inkinobho ethi Hlanganisa ngqo, noma, uma uqaphela kakhulu, khetha i-Transform Data ukuze uhlunge uhlu lwamafayela (isibonelo, ukunganaki amafolda athile) ngaphambi kokuqhubeka nokuhlanganisa.
Uma usufake ibhokisi lengxoxo lokuhlanganisa, i-Power Query isebenzisa ifayela lesampula ukuhlaziya isakhiwo . Ngokuzenzakalelayo, ikhetha ifayela lokuqala ohlwini, kodwa ungayishintsha uma kukhona elinye elimelela kangcono isethi yedatha. Lapha, ungakhetha inketho yokweqa amafayela anamaphutha , okuwukusindisa impilo lapho umbiko wonakele noma ungenalutho, okuvimbela yonke inqubo ukuthi ime.
Ukuphathwa kokukhipha kanye nokuhlanzwa kwedatha
Ngemva kokwamukela ukuhlanganiswa, uhlelo lusebenza ngomlingo wobuchwepheshe ngemuva kwezigcawu: ludala umbuzo wesampula kanye nomsebenzi ohlukaniswe ngamapharamitha. Lokhu kusho ukuthi noma yiziphi izinguquko ozenzayo kufayela lesampula zizokopishwa ngokuzenzakalelayo kuwo wonke amanye amafayela kufolda. Uma, isibonelo, amafayela akho e-CSV enemigqa emine yama-header angadingekile ngaphambi kwedatha yangempela, ungaya kumbuzo wokuguqulwa bese usebenzisa inketho yokususa imigqa ephezulu ukuze uhlanze lokho kugcwala.
Ngemva kokuhlanza imigqa, kubalulekile ukusebenzisa umsebenzi othi " Sebenzisa umugqa wokuqala njengezihloko" . Khumbula ukuthi uma wenza lokhu, i-Power Query ivame ukwengeza isinyathelo sokuguqula uhlobo lwedatha ngokuzenzakalelayo. Uma, lapho ubuyela kumbuzo oyinhloko, ubona iphutha elithi ikholomu ethi 'Ikholomu1' ayitholakali, ungakhathazeki; lokhu kuvamile ngoba uqambe kabusha amakholomu esibonelweni , futhi isinyathelo esilandelayo asisawaqapheli amagama amadala. Mane nje ususe leso sinyathelo esingalungile kuphaneli ye-Applied Steps, futhi konke kuzobuyela esimweni esijwayelekile.
Izinselele ezivamile kanye nezinye izindlela ezithuthukisiwe
Ngezinye izikhathi impilo ayilula kangako, futhi sihlangana namafayela angafani. Isibonelo, uma unemibiko ye-ServiceNow lapho i-header eyodwa ibizwa ngokuthi 'resolved_on' kanti enye ibizwa ngokuthi 'closed_on', i-Power Query ingakunikeza izinkinga ngoba idinga isakhiwo esivumelanayo . Uma ifayela lesampula lingahambisani kahle nokunye, ithuluzi lingase likhiphe ulwazi kumafayela angahambelani noma lilahle amaphutha okungenisa.
Ezimweni lapho isakhiwo sididekile ngokuphelele noma ivolumu yedatha inkulu kakhulu (idlula imigqa ye-Excel eyisigidi), kunezinye izinketho. Abanye abasebenzisi bakhetha izikripthi ze-VBA besebenzisa izixhumi ze-CSV , ezivumela amafayela ukuthi ahlanganiswe ngaphandle kokuthi ama-header aphindaphindwe, kungakhathaliseki ukuthi i-delimiter yensimu noma yerekhodi iyahlukahluka kusuka kufayela kuya kwelinye. Ngenkathi i-Windows Shell ishesha kakhulu, ivame ukwehluleka lapho isingatha ukuqhekeka kwemigqa efakiwe, kanti ikhambi eliqinile le-VBA aligcwalisi i-RAM kakhulu futhi lingaphatha izigidi zamarekhodi kalula.
Ukuze uqedele inqubo, uma idatha isihlanganisiwe ku-Power Query, khumbula ukunikeza uhlobo lwedatha olufanele kukholomu ngayinye (Usuku lwezinsuku, Imali engenayo, njll.) ngaphambi kokulayisha ithebula lokugcina. Ungaqinisekisa ukuthi konke kulungile ngokuhlunga ikholomu ye-Source Name ukuqinisekisa ukuthi wonke amafayela kufolda acutshungulwe ngempumelelo.
Ikhono lokwenza ngokuzenzakalelayo ukuhlanganiswa kwamafayela amaningi e-CSV likuvumela ukuthi usuke emsebenzini wesandla oyisicefe uye emsebenzini wobungcweti, kungaba ngokusebenzisa ukuguquguquka kwe-Power Query yokuhlanza idatha noma ukusebenzisa i-VBA lapho izakhiwo zingahambisani noma amavolumu edlula imikhawulo yamaspredishithi.