Useiden Excel-arkkien liittäminen LuckyTemplatesiin

Tässä opetusohjelmassa käsitellään useita taulukoita sisältävän Excel-tiedoston tuomista ja avaamista yhteen LuckyTemplates-taulukkoon.

Tätä Excel-tietotiedostoa käytetään tässä opetusohjelmassa.

Tiedosto sisältää 60 arkkia tietoja samalla asettelulla.

Tämän opetusohjelman tavoitteena on ladata kaikki Excel-taulukot LuckyTemplates-sovellukseen ja hakea Bond-tiedot yhtenä taulukkona. Sinun on hankittava suojausmerkki ja lisättävä se erilliseen sarakkeeseen. Liitä sitten ne kaikki yhteen taulukkoon, joka hyödyntää käyttöliittymää tarvitsemasi koodin luomiseen.

Sisällysluettelo

• Excel-taulukoiden tuominen LuckyTemplatesiin
• Turvallisuusmerkin tuominen Excelistä LuckyMalleihin
• Tyhjien rivien poistaminen
• Kyselyn kopioiminen
• Tietotyyppien määrittäminen sarakkeisiin
• Security Ticker -sarakkeen lisääminen Excelistä LuckyMalleihin
• Mukautetun sarakkeen luominen joukkovelkakirjakyselylle
• Johtopäätös

Excel-taulukoiden tuominen LuckyTemplatesiin

Ensimmäinen asia on avata ja luoda parametri tiedoston sijainnin tallentamiseksi Hallinnoi parametreja -vaihtoehtoa käyttämällä.

Napsauta Hallitse parametreja -ruudussa Uusi ja syötä parametrin nimeksi FileLocation . Aseta seuraavaksi tietotyypiksi Teksti ja ehdotetuiksi arvoiksi Mikä tahansa arvo . Kopioi Excel-tiedoston tiedostopolku ja liitä se Nykyinen arvo -kenttiin.

Kun olet painanut OK, napsauta Uusi lähde ja valitse Excel-työkirja .

Valitse seuraavaksi ja avaa Excel-tiedosto. Kun olet valmis, Navigator-näyttö avautuu ja luettelee kaikki Excel-tiedoston arkit.

Valitse ensimmäinen arkki ja paina OK. Sen jälkeen näet taulukon Power Query Editorissa.

Seuraava asia on muuttaa kovakoodatun tiedoston polku tiedostoparametriksi. Avaa Advanced Editor -ikkuna ja muuta lähdekoodin tiedostopolkuksi FileLocation .

Tietosi on parasta säilyttää Excel-taulukoissa, koska rajat tai tietoalue määritellään ennen kuin tuot ne. Koska tiedosto sisältää taulukoita eikä taulukoita, se saattaa tuoda tyhjiä sarakkeita ja rivejä, joten sinun on järjestettävä ne.

Turvallisuusmerkin tuominen Excelistä LuckyMalleihin

Tietäen, että arkkien asettelu on kiinteä, auttaa ratkaisun rakentamisessa, varsinkin jos haluat maksimoida ja käyttää käyttöliittymää koodin luomiseen. Esimerkiksi yksi vaatimus on lisätä sarake, joka sisältää suojaustunnisteen. Jos katsot tietoja, näet tickerin.

Käyttöliittymä voi auttaa sinua poimimaan tickerin arvon. Napsauta solua hiiren kakkospainikkeella ja valitse Lisää uutena kyselynä .

Kaavapalkissa näet, että taulukon nimen perässä on nollapohjainen rivinumero kiharoiden suluissa. Kaarevia sulkeita kutsutaan paikkaindeksioperaattoreiksi . Näet myös kentän nimen hakasulkeiden sisällä, joita kutsutaan kenttäkäyttöoperaattoreiksi .

Näillä koodeilla voit nyt poimia arvon. Palaa Bond-taulukkoon ja hanki Bond-tiedot. Poista ensin 8 ylintä kahdeksan riviä. Napsauta Poista rivit ja valitse Poista ylimmät rivit .

Syötä seuraavaksi rivien lukumäärä -kenttään 8 ja paina OK.

Napsauta sitten Käytä ensimmäistä riviä otsikoina määrittääksesi otsikot.

Kun se on valmis, Bond-pöytä näyttää nyt tältä.

Suoraan otsikon alapuolella näet ohuen viivan, joka edustaa sarakkeen laatua. Sieltä näet, että sarakkeissa on melko paljon tyhjiä paikkoja. Tämä tarkoittaa, että tiedosto toi paljon tyhjiä rivejä.

Tyhjien rivien poistaminen

Poista tyhjät rivit napsauttamalla Poista rivit ja valitsemalla Poista tyhjät rivit .

Tämä muunnos luo tämän syntaksin:

Record.FieldValues ​​saa kaikki arvot taulukon nykyiseltä riviltä luettelona. List.RemoveMatchingItems poistaa kaikki arvot ensimmäisestä luettelosta, jotka vastaavat toista luetteloa. Toisessa luettelossa on vain tyhjä tekstimerkkijono tai nolla. Nämä ovat arvot, jotka jätetään pois ensimmäisestä luettelosta.

Jos kaikki tyhjät tekstijonot ja nollat ​​on poistettu luettelosta tietuekenttäarvojen kanssa, luettelon tulee olla tyhjä ja List.IsEmpty-arvon arvoksi tulee True. Sitten Table.SelectRows säilyttää Trues-arvot.

Sinun ei pitäisi päätyä taulukkoon, jossa on vain tyhjiä rivejä. Tästä syystä avainsana ei lisätään ennen List.IsEmpty . Se palauttaa sitten taulukon, joka sisältää ei-tyhjiä rivejä.

Tyhjien rivien lisäksi sinun on poistettava myös tyhjät sarakkeet. Mutta ennen sitä katso, mitä Power Query luo, kun poistat sarakkeen. Valitse neljäs sarake ja napsauta Poista sarakkeet .

Kun olet suorittanut muunnoksen, näet tämän syntaksin kaavapalkissa.

Se kutsuu Table.RemoveColumns -funktiota ja viittaa ja välittää edellisen vaiheen Applied Steps -ruudussa ensimmäisenä argumenttina. Muunnos välittää lisäksi luettelon, joka sisältää poistettavien sarakkeiden sarakkeiden nimet.

Kyselyn kopioiminen

Kopioi nyt kysely ja valitse Sovelletut vaiheet -ruudusta Promote Headers -vaihe. Napsauta sitten hiiren kakkospainikkeella kyseistä vaihetta ja valitse Poista loppuun asti .

Muista, että voit käyttää paikkaindeksioperaattoria välittääksesi nollapohjaisen rivinumeron kierteisissä hakasulkeissa. Säilytä siis vain ensimmäinen rivi syöttämällä 0 kaavapalkin kahden kiharan hakasulkeen sisään.

Kun se on valmis, Bond-pöytä näyttää tältä.

Käytä sieltä uudelleen osaa Poistetut tyhjät rivit -vaiheessa luodusta logiikasta muuttaaksesi tietueen luetteloksi ja poistaaksesi nollakohdan. Palaa Bond-kyselyyn ja valitse Poista tyhjät rivit -vaihe. Kopioi seuraavaksi tämä M-koodi.

Palaa kopioituun kyselyyn ja liitä koodi kaavapalkkiin. Järjestä sitten joitain merkkijonoja, jotta kaava näyttää tältä.

Olet nyt luonut luettelon sarakkeiden nimistä, jotka haluat säilyttää. Nimeä kysely uudelleen Sarakkeiden nimet .

Palaa sitten takaisin Bond-kyselyyn. Koska olet luonut kyselyn, joka sisältää kaikki sarakkeet, jotka haluat säilyttää, sinun on lueteltava kaikki sarakkeet, jotka haluat sulkea pois Table.RemoveColumns -funktiossa .

Muuta {Sarake4} samalla syntaksilla, joka kopioitiin Poistetut tyhjät rivit -vaiheista. Muuta myös {"", null} arvoksi ColumnNames .

Sitten sinun on välitettävä luettelo Bond-taulukon sarakkeiden todellisista nimistä. Muuta Record.FieldValues(_) arvoksi Table.ColumnNames() . Syötä #”Poistetut tyhjät rivit” sulkeisiin välittääksesi taulukon viiteargumentin.

Tietotyyppien määrittäminen sarakkeisiin

Seuraavaksi on määritettävä sarakkeille sopivat tietotyypit. Napsauta Päivämäärä-sarakkeessa otsikon vieressä olevaa kuvaketta ja valitse Päivämäärä.

Valitse sarakkeille PX_LAST ja YLD_YTM_MID tietotyyppi Desimaaliluku.

Näiden kolmen kyselyn avulla olet luonut kaikki rakennuspalikat, joiden avulla voit suunnitella ratkaisun, joka koskee kaikkia tiedoston taulukoita. Tätä varten sinun on kopioitava Bond-kysely ja poistettava kaikki vaiheet paitsi Lähde-vaihe Applied Steps -ruudussa.

Security Ticker -sarakkeen lisääminen Excelistä LuckyMalleihin

Lähde-vaiheessa näet kaikki Excel-tiedoston sisältämät tiedot. Sen sijaan, että luot erillisen kyselyn jokaiselle arkille, voit käyttää Bond-kyselyä ja muuttaa Data-sarakkeen sisäkkäisiä taulukoita.

Lisää ensin suojausmerkki. Jos napsautat tyhjää tilaa minkä tahansa taulukon solun sisällä, näet esikatselun kyseisen taulukon sisällöstä.

Sinun on luotava logiikka käskyllä ​​saadaksesi suojausmerkin. Siirry Sarake2-kyselyyn ja kopioi suojausmerkin osoite kaavapalkista.

Palaa seuraavaksi Bond-kyselyyn ja lisää mukautettu sarake.

Nimeä sarake Security Ticker ja kirjoita seuraava M-koodi.

Kaavassa on IF- lause, jossa sanotaan, että jos sana Turvallisuus löytyy sarakkeesta 1, se antaa sen viereisen sarakkeen 2 solun arvon. Muussa tapauksessa annetaan nolla.

Kun olet painanut OK, taulukkoon lisätään uusi sarake, jossa on suojausmerkit.

Napsauta Security Ticker -sarakkeen avattavaa painiketta ja poista nolla-kohdan valinta poistaaksesi kaikki sarakkeen nollakohdat.

Sen jälkeen sinulle jää kaikki joukkovelkakirjatiedot kaikilta arkeilta. Sinun tarvitsee vain toistaa Bond(2)-kyselyssä tehdyt muunnokset ja käyttää niitä Data-sarakkeen sisäkkäisissä taulukoissa.

Mukautetun sarakkeen luominen joukkovelkakirjakyselylle

Siirry Bond(2)-kyselyyn, avaa Advanced Editor -ikkuna ja kopioi seuraava koodi:

Palaa seuraavaksi Bond-kyselyyn ja luo toinen mukautettu sarake. Koska sinun on käytettävä useita muunnoksia useissa vaiheissa, sinun on käytettävä let -lausetta. Joten syötä let ja liitä koodi Advanced Editorista.

Muuta sitten Bond_Sheet arvoksi Data muuttaaksesi Data-sarakkeen taulukon.

Kun olet painanut OK, taulukkoon lisätään uusi sarake.

Jos napsautat välilyöntiä minkä tahansa sarakkeen solun sisällä, näet Bond(2)-kyselyn tiedot.

Kaikki mitä tarvitset on Bond-kyselyn kahdessa viimeisessä sarakkeessa. Valitse siis sekä Security Ticker- että Custom-sarakkeet ja napsauta Poista muut sarakkeet Poista sarakkeet -nauhasta.

Laajenna Mukautettu sarake ja poista valinta Käytä alkuperäistä nimeä etuliitteenä -ruudusta.

Kun olet valmis, vahvista taulukko. Valitse taulukon alta Sarakeprofilointi koko tietojoukon perusteella -vaihtoehto.

Tämän muunnoksen jälkeen Data-sarakkeessa tapahtuu virhe.

Joten tutki virhettä napsauttamalla Tiedot-saraketta ja valitsemalla Säilytä virheet -vaihtoehdon Säilytä rivit -nauhasta.

Jos napsautat Virhe-arvoa Tiedot-sarakkeessa, voit lukea alla olevan virheilmoituksen:

Voit ratkaista ongelmat poistamalla ensin Säilytetyt virheet -vaiheen. Napsauta Tiedot-saraketta ja valitse Poista virheet Poista rivit -nauhasta.

Aseta sitten sarakkeen profilointivaihtoehto takaisin 1000 ylimpään riviin. Ja siinä se!

Johtopäätös

Tämän käyttöliittymäpohjaisen ratkaisun avulla voit liittää useita arkkeja Excel-tiedostosta LuckyTemplates-sovellukseen. Sen sijaan, että luotaisiin 60 erillistä kyselyä ja suoritettaisiin kaikki muunnokset toistuvasti, tämä ratkaisu voi luoda yhden kyselyn, joka suorittaa kaikki muunnokset. Käytä ja maksimoi tämä ratkaisu .

Melissa