Nthawi zina pamakhala zochitika pomwe sizidziwika pasadakhale kuti zingati komanso mizere iti yomwe iyenera kutumizidwa kuchokera kumagwero a data. Tiyerekeze kuti tikuyenera kukweza deta kuchokera ku fayilo ya malemba kupita ku Power Query, yomwe, poyang'ana koyamba, sikupereka vuto lalikulu. Vuto ndiloti fayiloyo imasinthidwa nthawi zonse, ndipo mawa ikhoza kukhala ndi mizere yosiyana ndi deta, mutu wa atatu, osati mizere iwiri, ndi zina zotero:
Ndiko kuti, sitinganene pasadakhale motsimikiza, kuyambira mzere uti komanso ndendende mizere ingati yomwe iyenera kutumizidwa kunja. Ndipo ili ndi vuto, chifukwa magawowa ndi ovuta-code mu M-code ya pempho. Ndipo ngati mupempha fayilo yoyamba (kuitanitsa mizere 5 kuyambira pa 4), ndiye kuti sichidzagwiranso ntchito moyenera ndi yachiwiri.
Zingakhale zabwino ngati funso lathu likhoza kudziwa chiyambi ndi mapeto a mawu "oyandama" kuti alowe.
Yankho lomwe ndikufuna kunena likuchokera pa lingaliro lakuti deta yathu ili ndi mawu osakira kapena mfundo zomwe zingagwiritsidwe ntchito ngati zolembera (ziwonetsero) za chiyambi ndi mapeto a chipika cha deta chomwe tikufuna. Mu chitsanzo chathu, chiyambi chidzakhala mzere woyambira ndi mawu SKU, ndipo mapeto ndi mzere ndi mawu Total. Kutsimikizika kwa mzerewu ndikosavuta kukhazikitsa mu Power Query pogwiritsa ntchito gawo lokhazikika - analogue ya ntchitoyi IF (NGATI) pa Microsoft Excel.
Tiyeni tiwone momwe tingachitire.
Choyamba, tiyeni tiyike zomwe zili mufayilo yathu mu Power Query m'njira yokhazikika - kudzera mu lamulo Deta - Pezani zambiri - Kuchokera pafayilo - Kuchokera pamafayilo / CSV (Deta - Pezani Zambiri - Kuchokera pafayilo - Kuchokera pamafayilo / CSV). Ngati muli ndi Power Query yoyika ngati chowonjezera chosiyana, ndiye kuti malamulo ofananirawo adzakhala pa tabu Kufunsa Mphamvu:
Monga nthawi zonse, mukamaitanitsa, mutha kusankha munthu wolekanitsa ndime (kwa ife, iyi ndi tabu), ndipo mutatha kuitanitsa, mutha kuchotsa gawo lomwe langowonjezera. mtundu wosinthidwa (Mtundu Wosinthidwa), chifukwa ndikutali kwambiri kuti tigawire mitundu ya data kumagawo:
Tsopano ndi lamulo Kuwonjezera Mzere - Conditional Column (Onjezani Gawo - Conditional Column)tiyeni tiwonjezere ndime ndikuwunika zikhalidwe ziwiri - kumayambiriro ndi kumapeto kwa chipikacho - ndikuwonetsa zikhalidwe zosiyanasiyana pazochitika zilizonse (mwachitsanzo, manambala 1 и 2). Ngati palibe zikhalidwe zomwe zakwaniritsidwa, ndiye zotuluka null:
Pambuyo pang'anani OK timapeza chithunzi chotsatira:
Tsopano tiyeni tipite ku tabu. Transformation ndikusankha gulu Dzazani - Pansi (Sinthani - Dzazani - Pansi) - athu ndi awiri adzatambasula pansi:
Chabwino, ndiye, momwe mungaganizire, mutha kungosefa mayunitsi omwe ali mugawo lokhazikika - ndipo nayi gawo lathu lomwe timasilira:
Chotsalira ndikukweza mzere woyamba kumutu ndi lamulo Gwiritsani ntchito mzere woyamba ngati mitu tsamba Kunyumba (Kunyumba - Gwiritsani Ntchito Mzere Woyamba Monga Mitu) ndi kuchotsa gawo losafunika lokhazikika podina kumanja pamutu wake ndikusankha lamulo Chotsani ndime (Chotsani Mzere):
Vuto lathetsedwa. Tsopano, posintha zomwe zili mufayilo yoyambira, funsoli tsopano lidziyimira pawokha chiyambi ndi kutha kwa gawo "loyandama" la zomwe tikufuna ndikulowetsa mizere yolondola nthawi iliyonse. Zachidziwikire, njirayi imagwiranso ntchito pakulowetsa XLSX, osati mafayilo a TXT, komanso potumiza mafayilo onse kuchokera pafoda nthawi imodzi ndi lamulo. Deta - Pezani zambiri - Kuchokera pafayilo - Kuchokera mufoda (Deta - Pezani Zambiri - Kuchokera pafayilo - Kuchokera mufoda).
- Kusonkhanitsa matebulo kuchokera kumafayilo osiyanasiyana pogwiritsa ntchito Power Query
- Kupanganso crosstab kuti ikhale yosalala yokhala ndi ma macros ndi Power Query
- Kupanga Tchati cha Project Gantt mu Power Query