Microsoft Power Queryn opetusohjelma erilaisten, kiinteään sarakkeen leveyteen liittyvien ongelmien korjaamisesta

Mudassir: Meillä on tänään erittäin mielenkiintoinen ongelma työskenneltävänä. Tämän tiedoston ongelmana on, että se on korjattu sarakkeilla erotettuna, enkä tiedä kuinka ratkaista tämä Microsoft Power Queryn avulla. Voit katsoa tämän opetusohjelman koko videon tämän blogin alaosasta.

Ensinnäkin minun ei ollut helppoa poistaa sarakkeita dynaamisesti. Toiseksi tässä raportissa meillä on yksi taulukko, jolla on eri sarakkeen leveys, ja sitten toinen taulukko, jolla on eri sarakkeen leveys.

Joten jos laittaisin yhden erottimen dynaamisesti päälle, en pystyisi saamaan tietoja siististi. Haluan saada kaikki nämä tiedot toisesta taulukosta ja tuotenumeroni ensimmäisestä taulukosta. Haluan myös työnumeron kaikkien taulukoiden jokaiselle riville.

Yritin ratkaista sen itse, mutta koska tämä liittyy jotenkin tehokyselyyn, tarvitsin apua Melissalta. Luulin, että siihen menee vähintään kaksi päivää, mutta hän onnistui keksimään ratkaisun heti.

Melissa näyttää meille, kuinka hän ratkaisi tämän monimutkaisen ongelman. Uskon, että useimmat ihmiset käsittelevät tämäntyyppisiä ongelmia ja etsivät tapoja ratkaista ne.

Melissa: Ensimmäinen vinkki on, jos tarkastelet kiinteäpituista tiedostoa, voit siirtyä Näytä-välilehdelle ja ottaa Monospaced- asetuksen käyttöön.

Voimme nähdä, että se on kiinteäpituinen fontti. Näemme myös otsikot, alkutaulukot ja alitaulukot. Nämä ovat osia, joista olemme kiinnostuneita ja joista haluamme poimia.

Varmista myös, että kaavapalkki on käytössä. On aina hyvä, että se näkyy näytölläsi, koska käytämme sitä usein tehdäksemme pieniä muutoksia syötteeseen.

Olen luonut parametrin tiedoston sijainnille, johon olen tallentanut CSV-tiedoston. Toin sen esitystiedostona ja lisäsin sen tiedoston sijaintiparametriin. Sitten loin viitteen ja jatkan siitä viittauksesta. Joten sitä tarkastelemme juuri nyt Microsoft Power queryssä.

Sisällysluettelo

• Hakemistosarakkeen lisääminen
• Puskuriluettelon luominen Microsoft Power Queryssä
• Otsikkojen jakaminen riveistä
• Nolla- ja tyhjäkohtien poistaminen Microsoft Power Querysta
• Osioiden jakaminen
• Tekstimerkkijonojen leikkaaminen Microsoft Power Queryssa
• Johtopäätös

Hakemistosarakkeen lisääminen

Yleensä kun aloitan työstämään tällaista tiedostoa, minun on tiedettävä asiakkaan vaatimukset. Kysyn, mitä asiakas tarvitsee ja mitä etsiä.

Tässä tapauksessa haluamme otsikoista tuotenumeron ja työnumeron, ja sitten haluamme kaikki kyseiseen otsikkoon kuuluvat tiedot.

Tarvitsemme avaimen tuodaksemme nämä asiat takaisin yhteen. Mutta jos avainta ei ole, minun tehtäväni on lisätä . Napsautan minitaulukkokuvaketta, valitsen Lisää hakemistosarake ja lisään sitten From 0 .

Käytän logiikkaa myöhemmin luetteloissa, joissa on 0-pohjainen indeksi. Indeksin aloittaminen nollasta tarkoittaa itse asiassa, että voit viitata samaan riviin. Muussa tapauksessa sinun on vähennettävä 1 päästäksesi kyseiseen 0-pohjaiseen sijaintiin.

Sitten meidän on löydettävä paikka, jossa otsikot ovat, minkä voimme tehdä melko helposti, koska otsikot toistuvat jatkuvasti koko tiedoston ajan.

Aloita kopioimalla tämä arvo:

Lisää uusi tyhjä kysely, liitä se ja kutsu tätä HeaderID:ksi .

Teen saman prosessin alitaulukoille. Kopioin tuon tekstimerkkijonon, luon toisen tyhjän kyselyn ja liitän sen arvon sisään. Tätä merkkijonoa käytämme etsiessämme yksityiskohtaisia ​​rivejä.

Jos tämä prosessi jollakin tavalla muuttaa jonkin näistä taulukoista otsikon, minun tarvitsee vain vaihtaa jokin tekstimerkkijonoista ja tiedosto toimii taas.

Minun ei todellakaan tarvitse sukeltaa M-koodiin etsiäkseni sitä merkkijonoa, jota etsimme. Voimme käyttää tätä vain parametrina.

Otetaan näiden kahden kyselyn lataus käyttöön.

Puskuriluettelon luominen Microsoft Power Queryssä

Ensimmäinen asia, jonka teen, on muuttaa Column1 luetteloksi viittaamalla ja lataamalla se muistiin kerran. Tällä tavalla minun ei tarvitse soittaa tiedostoon toistuvasti.

Avaan edistyneen editorin ja sijoitan sen kokonaan yläreunaan. Kun käytät käyttöliittymää koodin rakentamiseen, se viittaa edelliseen vaiheeseen.

Kun sijoitat puskurivaiheen mihin tahansa muualle koodiisi ja haluat tehdä muutoksia myöhemmin, se auttaa sinua tekemään muutokset manuaalisesti luotavaan vaiheeseen.

Kutsun tätä puskuriluetteloa ja viittaan sarakkeeseen 1. Ladatakseni sen muistiin lisään List.Buffer- vaiheen.

Tässä on muuttujani aina ylhäällä. Voin viitata siihen yhä uudelleen ja uudelleen.

Ensimmäinen asia, jonka haluan määrittää, on, mistä otsikoni alkavat, koska tarvitsen avaimen pitääkseni nämä otsikkoosat ja saadakseni yhden arvon kaikille noille riveille. Tätä varten lisään mukautetun sarakkeen ja kutsun sitä otsikoksi .

Kirjoitan, että jos sarake1 on sama kuin otsikkotunnuksemme, haluan indeksinumeroni olevan tyhjä.

Tuloksena se löysi tekstin ja palautti numerot 5 ja 23.

Tarvitsen tämän arvon kaikilla riveillä, joten minun on täytettävä se. Voit napsauttaa hiiren kakkospainikkeella täyttääksesi, mutta voit myös käyttää hyvin yksinkertaista syntaksia ja lisätä sen kaavapalkkiin.

Tässä tapauksessa lisäsin Table.FillDown ja tekstimerkkijonoon ilmoitin minkä sarakkeen haluamme täyttää (Header).

Nyt olemme täyttäneet sen kaikille riveille. Meillä on avain kaikille otsikko- ja riviosioille, koska niillä kaikilla on sama arvo.

Otsikkojen jakaminen riveistä

Seuraava vaihe on jakaa otsikot riveistä. Lisään toisen mukautetun sarakkeen ja kutsun sitä Temp . Tällä kertaa teemme jotain yksityiskohtaisempaa ja hyödynnämme aiemmin luomaani puskuriluetteloa .

Käytämme paria luettelotoimintoa tarkastellaksemme kutakin paikkaa ja löytääksemme, löytyykö indeksiä vastaavuus.

Aloitan if-lauseella ja käytän List.Contains- sovellusta etsiäkseni tiettyä sijaintia puskuriluettelosta ja viitaten kyselyyn HeaderID .

Haluamme löytää sen tiedoston koko pituudelta ja palauttaa sitten kohteen sijainnin luettelossa. Jos se vastaa hakemistoa, meillä on vastaavuus kyseiselle riville.

Sitten haluan palauttaa arvon otsikon tunnistamiseksi. Tässä tapauksessa palautan vain H:n. Kopion syntaksin, jotta minun ei tarvitse kirjoittaa sitä uudelleen.

Meidän on myös tunnistettava riviosio. Jos luettelo ei sisällä HeaderID:tä vaan DetailID:tä , olemme riviosassa.

Jos sarake1 on tyhjä tekstimerkkijono, haluan sen pysyvän tyhjänä . Jos näin ei ole, haluan sen olevan tyhjä .

Tämä oppositio sai otsikkorivin ja palautti H:n, ja sitten se löysi yksityiskohtaisen rivin ja palautti R:n. Sitten se palautti 0:t kaikille kohteille, jotka jaetaan kyseisessä riviosassa.

Nämä tyhjät kohdat tai nollakohdat ovat tärkeitä, koska niiden avulla voit täyttää. Täyttö ei siirry tyhjien solujen yli, joten voimme poistaa ne myöhemmin.

Teemme tämän kaavapalkissa ja käytämme Table.FillDown -toimintoa uudelleen. Se haluaa luettelon sarakkeen nimellä, joka on Temp- sarakkeemme.

Nyt meillä on H- ja R-arvot toistettu koko tässä sarakkeessa, mikä tarkoittaa, että voimme itse asiassa jakaa otsikot yksityiskohtaisista osioista.

Voit myös täyttää sen käyttöliittymästä, jos et halua kirjoittaa koodia. Voit napsauttaa hiiren kakkospainikkeella ja valita Täytä ja sitten Alas .

Nolla- ja tyhjäkohtien poistaminen Microsoft Power Querysta

Nyt kun meillä on tämä oikeus, voimme poistaa asiat, joita emme tarvitse. Kaikki, mikä on nollaa tai sisältää tyhjän kohdan, ovat rivejä, joita emme tarvitse ja jotka on poistettava. Voimme poistaa ne suodattamalla.

Osioiden jakaminen

Kun poistamme nämä tyhjät ja tyhjät kohdat, meille jää kaikki tarvitsemamme. Tässä vaiheessa voimme vain jakaa osiot. Voimme keskittyä otsikkoriveihin ja valita ne, koska niillä on erillinen väli kaikista tietoriveistä (joilla on myös erillinen väli).

Lisään kaavapalkkiin uuden vaiheen, jonka avulla voin luoda toisen suodattimen samaan sarakkeeseen. Tässä tapauksessa säilytän vain kaikki otsikkoosat.

Nyt minulla on kaikki nuo otsikkorivit täällä.

Voin valita Column1 , siirtyä kaavapalkkiin, valita Jaa sarake ja jakaa sitten paikkojen mukaan.

Anna sitten power query itse selvittää tämä. Se ehdottaa paria asentoa. Hyväksy nämä paikat napsauttamalla OK .

Ainoat asiat, joista olemme kiinnostuneita otsikoista, ovat nimike ja työnumero .

Tässä kaavapalkissa voin nimetä ne uudelleen nimikkeellä ja työnumerolla . Tämä säästää minut toisesta sarakkeen uudelleennimeämisvaiheesta.

Tämän vaiheen jälkeen minun tarvitsee vain valita kohde , valita työnumero ja tietysti valita otsikkonäppäimemme . Sitten poistan kaikki muut sarakkeet, koska en enää tarvitse niitä.

Tämä tulee olemaan tulos. Meidän on vielä siivottava arvot ja poistettava tekstikohta ja viivat. Haluamme vain ne arvot siltä väliltä.

Joten avaamme tämän ja poistamme väliviivojen ja kohteiden valinnan.

Nyt kaikki otsikot on tehty.

Meidän on tehtävä sama prosessi myös DetailID: lle . Minun on nimettävä nämä vaiheet uudelleen, jotta minun on helpompi palata niihin hieman myöhemmin.

Palaamme alkuperäiseen kyselyyn, jonka aloitimme. Aloitimme suodatetuista riveistä Applied Steps -ruudussa.

Kopioin tämän ja lisään sen suodattimeeni. Tällä kertaa en valitse H:tä, mutta valitsen R:n.

Sitten valitsen Column1, siirryn Split-sarakkeeseen, jaan paikkojen mukaan ja pyydän Power queryä selvittämään sen.

Tätä power query ehdottaa. Kokeillaan tätä.

Tämä näyttää itse asiassa aika hyvältä. Jopa rivit yhteensä jakautuivat täydellisesti. Tietysti tiloja on paljon, koska meillä oli tuo sisennys.

Tekstimerkkijonojen leikkaaminen Microsoft Power Queryssa

Valitsen ensimmäisen sarakkeen ja painan sitten alas + vaihtonäppäintä valitaksesi sarake 1.10. Siirry kohtaan Transform , valitse Muotoile ja sitten Trim . Leikkaaminen poistaa ylimääräiset tilat vain merkkijonon edestä tai päästä, ei väliltä.

Seuraavaksi voimme vain mainostaa otsikoita, joten minun ei tarvitse kirjoittaa kaikkia näiden sarakkeiden otsikoita tai otsikoita. Jakamisvaiheessa nimesin kaksi saraketta uudelleen. Nyt, 10 sarakkeen kanssa, se on tietysti vähän vaivaa.

Meidän on myös päästävä eroon näistä ylimääräisistä arvoista. Koska meillä on kokonaissummat, minun on käytettävä jotakin näistä kolmesta viimeisestä sarakkeesta, koska ne ovat ainoita rivejä, joilla on lisäarvot jossain välissä. Sitten poistamme näiden välilyöntien, väliviivojen ja tekstien valinnan.

Sitten poistan tarpeettomat sarakkeet, joten jäljelle jää vain taulukko, jossa on vain otsikot ja vain tiedot. Tarvitsemme avaimen tuodaksemme nämä osat takaisin yhteen.

Tätä varten voimme käyttää itseliittämistä, jotta voimme yhdistää taulukon itsensä kanssa tuodaksemme tiedot takaisin yhteen. Valitse Aloitus -välilehdellä Yhdistä ja valitse sitten sarake 5 ja sama kysely.

AllDetailsin sijaan haluan AllHeadersin alkuperäiseksi taulukoksi, jonka kanssa haluan yhdistää.

Tämä on tuonut takaisin kaikki tiedot otsikkotaulukosta yhdellä rivillä jokaiselle tuotteelle ja jokaiselle työnumerolle.

Yhdisimme tietoriveihin avaimella. Jos painan sivulle tässä tyhjässä tilassa, näemme esikatselun kaikista otsikkoon 5 kuuluvista riveistä.

Poistamme viimeisen sarakkeen tästä ja sitten olemme korjaaneet sekoitettu kiinteä sarakkeen leveys Microsoft Power Queryssa.

Johtopäätös

Tässä opetusohjelmassa keksimme tavan ratkaista sekalaisia ​​kiinteän sarakkeen leveyden ongelmia Microsoft Power Queryn avulla. Jos pidit tämän opetusohjelman sisällöstä, älä unohda tilata LuckyTemplates-TV-kanava.

Meillä on jatkuvasti valtava määrä sisältöä minulta ja useilta sisällöntuottajilta, jotka kaikki ovat omistautuneet parantamaan tapaa, jolla käytät LuckyTemplatesia ja Power Platformia.

Melissa