Dzimwe nguva pane mamiriro ezvinhu apo zvisingazivikanwe pachine nguva kuti mangani uye ndeapi mitsara inoda kutorwa kunze kwenyika kubva kunobva data. Ngatitii isu tinofanirwa kurodha data kubva kune chinyorwa faira kupinda Power Query, iyo, pakutanga pakuona, hairatidze dambudziko rakakura. Chinetso ndechekuti faira rinogara richivandudzwa, uye mangwana rinogona kunge riine nhamba yakasiyana yemitsara ine data, musoro wevatatu, kwete mitsara miviri, nezvimwe.
Ndiko kuti, hatigone kutaura pachine chokwadi, kutanga kubva pamutsara upi uye kuti mitsetse mingani inoda kutengwa kunze kwenyika. Uye iri idambudziko, nekuti aya maparameter akaoma-coded muM-code yekukumbira. Uye kana iwe ukaita chikumbiro chefaira rekutanga (kuunza mitsara 5 kubva pa4), saka haichazoshanda nemazvo neyechipiri.
Zvingave zvakanaka kana kubvunza kwedu kuchigona kuona matangiro nemagumo e "zvinoyangarara" zvinyorwa zvekutumira.
Mhinduro yandinoda kupa yakavakirwa papfungwa yekuti data redu rine mamwe mazwi akakosha kana hunhu hunogona kushandiswa semamaki (maficha) ekutanga uye ekupedzisira kwedata block yatinoda. Mumuenzaniso wedu, mavambo anenge ari mutsetse unotanga neshoko SKU, uye kuguma mutsara une shoko muunganidzwa. Iyi mutsara yekusimbisa iri nyore kuita muSimba Query uchishandisa conditional column - analogue yebasa IF (KANA) muMicrosoft Excel.
Ngationei kuti tozviita sei.
Chekutanga, ngatiisei zviri mukati mefaira redu remavara muPower Query nenzira yakajairwa - kuburikidza nemurairo Dhata - Tora data - Kubva faira - Kubva pane zvinyorwa / CSV faira (Data - Tora Dhata - Kubva faira - Kubva pane zvinyorwa / CSV faira). Kana iwe uine Power Query yakaiswa seyakasiyana yekuwedzera-mukati, ipapo mirairo inoenderana ichave pane tab Simba Query:
Senguva dzose, kana uchipinza, unogona kusarudza iyo column separator character (kwedu, iyi tabhu), uye mushure mekutumira kunze, unogona kubvisa nhanho yakawedzerwa otomatiki. modified type (Changed Type), nekuti kuchiri kumberi kuti isu tipe mhando dzedata kumakoramu:
Zvino nemurairo Kuwedzera Column - Conditional Column (Wedzera Koramu - Conditional Column)ngatiwedzerei koramu nekutarisa maviri mamiriro - pakutanga uye pakupera kwechivharo - uye kuratidza chero akasiyana maitiro mune yega yega (semuenzaniso, nhamba 1 и 2) Kana pasina imwe yemamiriro ezvinhu inosangana, ipapo kubuda null:
Mushure mekunge mavharidza OK tinowana mufananidzo unotevera:
Zvino ngatiende kune tab. Kuchinja uye sarudza timu Zadza – Pasi (Shandura – Zadza – Pasi) - edu uye maviri achatambanudza pasi mbiru:
Zvakanaka, saka, sezvaungafungidzira, unogona kungosefa iwo mayunitsi mukoramu ine zvirevo - uye heino chidimbu chedu che data chaanoshuvira:
Chasara kusimudza mutsara wekutanga kune musoro nemurairo Shandisa mutsara wekutanga semusoro Tab Home (Kumba - Shandisa Row Yekutanga seMusoro) uye bvisa iyo isingakoshi yakawedzera mamiriro ekoramu nekudzvanya-kurudyi pamusoro wayo uye kusarudza iwo murairo Delete column (Delete Column):
Dambudziko ragadziriswa. Ikozvino, kana uchichinja iyo data mune sosi mameseji faira, mubvunzo uchazvimiririra wazvimiririra kutanga uye kupera kwe "inoyangarara" chidimbu che data yatinoda uye kupinza nhamba chaiyo yemitsara nguva yega yega. Ehe, nzira iyi inoshandawo panyaya yekupinza XLSX, kwete TXT mafaera, uye kana uchipinza mafaera ese kubva kune folda kamwechete nemurairo. Dhata - Tora data - Kubva faira - Kubva folda (Data - Tora Dhata - Kubva faira - Kubva folda).
- Kuunganidza matafura kubva akasiyana mafaera uchishandisa Power Query
- Kugadziridzazve crosstab kuti flat ine macros uye Power Query
- Kuvaka Project Gantt Chati mune Simba Query