Kupanga matebulo amitundu yambiri kuchokera papepala limodzi mu Power Query

Kupanga vuto

Monga zolowera, tili ndi fayilo ya Excel, pomwe limodzi lamasamba lili ndi matebulo angapo okhala ndi zogulitsa zamitundu iyi:

Zindikirani kuti:

• Matebulo amitundu yosiyanasiyana komanso magawo osiyanasiyana azinthu ndi zigawo m'mizere ndi mizati popanda kusanja.
• Mizere yopanda kanthu imatha kuyikidwa pakati pa matebulo.
• Chiwerengero cha matebulo akhoza kukhala aliwonse.
  

Malingaliro awiri ofunikira. Zimaganiziridwa kuti:

• Pamwamba pa tebulo lililonse, m'gawo loyamba, pali dzina la manejala amene malonda ake akuwonetsa (Ivanov, Petrov, Sidorov, etc.)
  
• Mayina a katundu ndi zigawo mu matebulo onse amalembedwa mofanana - ndi kulondola kwamilandu.
  

Cholinga chachikulu ndikusonkhanitsa deta kuchokera kumatebulo onse kukhala tebulo limodzi lokhazikika, losavuta kuti liwunikenso ndikumanga chidule, mwachitsanzo, ili:

Gawo 1. Lumikizani ku fayilo

Tiyeni tipange fayilo yatsopano ya Excel yopanda kanthu ndikusankha pa tabu Deta lamulo Pezani Zambiri - Kuchokera Fayilo - Kuchokera M'buku (Deta - Kuchokera pafayilo - Kuchokera m'buku lantchito). Tchulani komwe kuli fayilo yomwe ili ndi deta yogulitsa ndiyeno pazenera la navigator sankhani pepala lomwe tikufuna ndikudina batani. Sinthani Data (Sinthani Data):

Zotsatira zake, deta yonse kuchokera pamenepo iyenera kuikidwa mu Power Query editor:

Gawo 2. Chotsani zinyalala

Chotsani basi kwaiye masitepe mtundu wosinthidwa (Mtundu Wosinthidwa) и Mitu yokwezeka (Mitu Yokwezedwa) ndipo chotsani mizere yopanda kanthu ndi mizere yokhala ndi ziwopsezo pogwiritsa ntchito fyuluta null и TOTAL pa ndime yoyamba. Zotsatira zake, timapeza chithunzi chotsatira:

Khwerero 3. Kuwonjezera oyang'anira

Kuti mumvetsetse pambuyo pake komwe malonda ali, ndikofunikira kuwonjezera ndime patebulo lathu, pomwe pamzere uliwonse padzakhala dzina lofananira. Za ichi:

1. Tiyeni tiwonjezere gawo lothandizira ndi manambala a mzere pogwiritsa ntchito lamulo Onjezani Column - Index Column - Kuchokera ku 0 (Onjezani ndime - Index column - Kuchokera 0).

2. Onjezani ndime yokhala ndi fomula yokhala ndi lamulo Kuwonjezera Mzere - Mzere Wamakonda (Onjezani gawo - Mzere Wamakonda) ndikuwonetsa zomanga izi:

Lingaliro la chilinganizochi ndi losavuta - ngati mtengo wa selo lotsatira mugawo loyamba ndi "Katundu", ndiye kuti tapunthwa pa chiyambi cha tebulo latsopano, kotero timasonyeza mtengo wa selo yapitayi ndi dzina la manejala. Kupanda kutero, sitiwonetsa kalikonse, mwachitsanzo, null.

Kuti tipeze selo la kholo lomwe lili ndi dzina lomaliza, choyamba timalozera ku tebulo kuchokera pa sitepe yapitayi #"Mlozera wawonjezeredwa", ndiyeno tchulani dzina lagawo lomwe tikufuna [Ndime1] m'mabulaketi masikweya ndi nambala ya cell yomwe ili m'mabokosi opindika. Nambala ya selo idzakhala imodzi yocheperapo kuposa yomwe ilipo, yomwe timatenga kuchokera pamndandanda Index, motero.

3. Zimatsalira kudzaza ma cell opanda kanthu null mayina ochokera ku maselo apamwamba okhala ndi lamulo Sinthani - Dzazani - Pansi (Sinthani - Dzazani - Pansi) ndi kufufuta ndime yosafunikiranso yokhala ndi ma indices ndi mizere yokhala ndi mayina omaliza mgawo loyamba. Chifukwa chake, timapeza:

Khwerero 4. Kugawa m'magulu osiyana ndi mameneja

Chotsatira ndikuyika mizere ya manejala aliyense m'matebulo osiyana. Kuti muchite izi, pa Transformation tabu, gwiritsani ntchito Gulu ndi lamulo (Transform - Group By) ndipo pazenera lomwe limatsegulidwa, sankhani mzere wa Manager ndi ntchito Mizere Yonse (mizere yonse) kuti mungosonkhanitsa deta popanda kugwiritsa ntchito kuphatikizira. iwo (chiwerengero, pafupifupi, ndi zina). P.):

Zotsatira zake, timapeza matebulo osiyana a manejala aliyense:

Gawo 5: Sinthani Nested Tables

Tsopano timapereka matebulo omwe ali mu selo lililonse la mzere wotsatira Deta yonse mu mawonekedwe abwino.

Choyamba, chotsani mzati womwe sukufunikanso patebulo lililonse bwana. Timagwiritsanso ntchito Mzati mwamakonda tsamba Transformation (Sinthani - Mzere Wamakonda) ndi njira iyi:

Kenako, ndi gawo lina lowerengedwa, timakweza mzere woyamba patebulo lililonse pamitu:

Ndipo potsirizira pake, timapanga kusintha kwakukulu - kutsegula tebulo lililonse pogwiritsa ntchito M-function Table.UnpivotOtherColumns:

Mayina a zigawo kuchokera pamutu adzapita ku gawo latsopano ndipo tidzakhala ndi chocheperako, koma nthawi yomweyo, tebulo lalitali lokhazikika. Maselo opanda kanthu ndi null amanyalanyazidwa.

Kuchotsa mizati yapakatikati yosafunikira, tili ndi:

Khwerero 6 Wonjezerani Matebulo Osungidwa

Ikutsalirabe kukulitsa matebulo onse okhazikika kukhala mndandanda umodzi pogwiritsa ntchito batani lomwe lili ndi mivi iwiri pamutu wagawo:

... ndipo pamapeto pake timapeza zomwe timafuna:

Mutha kutumiza tebulo lotsatira kubwerera ku Excel pogwiritsa ntchito lamulo Kunyumba - Tsekani ndikunyamula - Tsekani ndikulowetsani… (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku…).

• Mangani matebulo okhala ndi mitu yosiyanasiyana kuchokera m'mabuku angapo
• Kusonkhanitsa deta kuchokera ku mafayilo onse mufoda yoperekedwa
• Kusonkhanitsa deta kuchokera pamasamba onse a bukhulo mu tebulo limodzi