Legge til flere ark i Excel til LuckyTemplates

Denne opplæringen vil diskutere hvordan du importerer og åpner en Excel-fil med flere ark til en LuckyTemplates-tabell.

Denne data Excel-filen vil bli brukt for denne opplæringen.

Filen inneholder 60 ark med data med samme layout.

Målet med denne opplæringen er å laste alle Excel-arkene til LuckyTemplates og hente obligasjonsinformasjonen som én tabell. Du må få sikkerhetstikken og legge den til i en egen kolonne. Deretter legger du alle sammen i en enkelt tabell som utnytter brukergrensesnittet for å generere koden du trenger.

Innholdsfortegnelse

• Importerer Excel-arkene til LuckyTemplates
• Få frem sikkerhetssymbolet fra Excel til LuckyTemplates
• Fjerning av tomme rader
• Dupliserer en spørring
• Tilordne datatyper til kolonner
• Legge til sikkerhetssymbolkolonne fra Excel til LuckyTemplates
• Opprette en egendefinert kolonne for obligasjonsspørring
• Konklusjon

Importerer Excel-arkene til LuckyTemplates

Det første du må gjøre er å åpne og lage en parameter for å lagre filplasseringen ved å bruke alternativet Administrer parametere .

I boksen Administrer parametere klikker du på Ny og skriver inn filplassering som parameternavn. Deretter setter du Datatype til Tekst og Foreslåtte verdier til Enhver verdi . Kopier filbanen til Excel-filen og lim den inn i Current Value-feltene.

Etter å ha trykket på OK, klikk på Ny kilde og velg Excel-arbeidsbok .

Deretter velger du og åpner Excel-filen. Når det er gjort, åpnes Navigator-skjermen og viser alle arkene i Excel-filen.

Velg det første arket og trykk OK. Etter det vil du se tabellen i Power Query Editor.

Den neste tingen å gjøre er å endre den hardkodede filbanen til filparameteren. Åpne Advanced Editor-vinduet og endre filbanen i kildekoden til FileLocation .

Det er best å ha dataene dine i Excel-tabeller fordi grensene eller dataområdet er definert før du tar dem inn i. Siden filen inneholder ark og ingen tabeller, risikerer den å bringe inn tomme kolonner og tomme rader, så du må ordne opp i det.

Få frem sikkerhetssymbolet fra Excel til LuckyTemplates

Å vite at utformingen av arkene er fikset hjelper med å bygge en løsning, spesielt hvis du ønsker å maksimere og bruke brukergrensesnittet til å generere koden. Et krav er for eksempel å legge til en kolonne som inneholder sikkerhetssymbolet. Hvis du ser på dataene, kan du se tickeren.

Brukergrensesnittet kan hjelpe deg med å trekke ut verdien av tickeren. Høyreklikk på cellen og velg Legg til som ny spørring .

I formellinjen vil du se at tabellnavnet er etterfulgt av et nullbasert radnummer mellom krøllede parenteser. De krøllede parentesene kalles posisjonsindeksoperatorer . Du vil også se feltnavnet omsluttet av hakeparenteser som kalles felttilgangsoperatører .

Med disse kodene kan du nå trekke ut verdien. Gå tilbake til obligasjonstabellen og få obligasjonsdataene. Fjern først de 8 øverste åtte radene. Klikk Fjern rader og velg Fjern øverste rader .

Skriv deretter inn 8 i feltet Antall rader og trykk OK.

Klikk deretter på Bruk første rad som overskrifter for å angi overskriftene.

Når det er gjort, vil Bond-bordet nå se slik ut.

Rett under overskriften vil du se en tynn linje som representerer kolonnekvaliteten. Derfra kan du se at det er ganske mange blanke i kolonnene. Dette betyr at filen brakte inn mange tomme rader.

Fjerning av tomme rader

For å fjerne de tomme radene, klikk Fjern rader og velg Fjern tomme rader .

Den transformasjonen genererer denne syntaksen:

Record.FieldValues ​​henter alle verdiene fra gjeldende rad i tabellen som en liste. List.RemoveMatchingItems fjerner alle verdiene i den første listen som har samsvar i den andre listen. Den andre listen inneholder bare en tom tekststreng eller en null. Dette er verdiene som vil bli ekskludert fra den første listen.

Hvis alle de tomme tekststrengene og null har blitt fjernet fra listen med postfeltverdiene, bør listen være tom og List.IsEmpty vil evalueres til True. Deretter vil Table.SelectRows beholde Trues.

Du bør ikke ende opp med en tabell med bare tomme rader. Det er derfor nøkkelordet ikke legges til før List.IsEmpty . Det returnerer da en tabell som inneholder ikke-tomme rader.

Bortsett fra tomme rader, må du også fjerne tomme kolonner. Men før det, ta en titt på hva Power Query genererer når du fjerner en kolonne. Velg den fjerde kolonnen og klikk Fjern kolonner .

Etter å ha utført transformasjonen, vil du se denne syntaksen i formellinjen.

Den kaller opp Table.RemoveColumns -funksjonen og refererer og sender det forrige trinnet i Applied Steps-panelet som første argument. Transformasjonen sender videre en liste som inneholder kolonnenavnet til kolonnene du vil fjerne.

Dupliserer en spørring

Dupliser nå spørringen og velg Promote Headers-trinnet i Applied Steps-panelet. Deretter høyreklikker du på det trinnet og velger Slett til slutt .

Husk at du kan bruke posisjonsindeksoperatoren til å sende et nullbasert radnummer i et sett med krøllede parenteser. Så behold bare den første raden ved å skrive inn 0 innenfor to krøllede parenteser i formellinjen.

Når det er gjort, vil Bond-bordet se slik ut.

Derfra gjenbruker du en del av logikken som ble opprettet av trinnet Removed Blank Rows for å gjøre posten om til en liste og fjerne null. Gå tilbake til Bond-søket og velg trinnet Fjern tomme rader. Deretter kopierer du denne M-koden.

Gå tilbake til den dupliserte spørringen og lim inn koden i formellinjen. Deretter ordner du noen strenger for å få formelen til å se slik ut.

Du har nå laget en liste med kolonnenavn som du vil beholde. Gi nytt navn til spørringen Kolonnenavn .

Gå deretter tilbake til Bond-søket. Siden du har opprettet en spørring som inneholder alle kolonnene du vil beholde, må du liste opp alle kolonnene du vil ekskludere i funksjonen Table.RemoveColumns .

Endre {Kolonne4} med samme syntaks som ble kopiert fra trinnene Fjernede tomme rader. Endre også {“”, null} til ColumnNames .

Deretter må du sende en liste med de faktiske kolonnenavnene fra Bond-tabellen. Endre Record.FieldValues(_) til Table.ColumnNames() . Skriv inn #”Removed Blank Rows” i parentesen for å sende et tabellreferanseargument.

Tilordne datatyper til kolonner

Den neste tingen å gjøre er å tilordne passende datatyper til kolonnene. For Dato-kolonnen klikker du på ikonet ved siden av overskriften og velger Dato.

For kolonnene PX_LAST og YLD_YTM_MID velger du datatypen Desimaltall.

Med disse tre spørringene har du laget alle byggeklossene du kan bruke til å designe en løsning som adresserer alle arkene i filen. For det må du duplisere Bond-spørringen og slette alle trinnene bortsett fra Kilde-trinnet i Applied Steps-panelet.

Legge til sikkerhetssymbolkolonne fra Excel til LuckyTemplates

I kildetrinnet kan du se alle dataene i Excel-filen. I stedet for å lage en separat spørring for hvert ark, kan du bruke Bond-spørringen og transformere de nestede tabellene i Data-kolonnen.

Først legger du til sikkerhetssymbolet. Hvis du klikker på det hvite rommet inne i cellen i en tabell, vil du se en forhåndsvisning av innholdet i den tabellen.

Du må lage en logikk ved å bruke en uttalelse for å få sikkerhetstickeren. Gå til kolonne 2-spørringen og kopier adressen til sikkerhetssymbolet fra formellinjen.

Gå deretter tilbake til Bond-søket og legg til en egendefinert kolonne.

Navngi kolonnen Security Ticker og skriv følgende M-kode.

Formelen har en IF -setning som sier at hvis ordet Sikkerhet finnes i Kolonne1, vil det gi verdien til cellen fra Kolonne2 som er ved siden av den. Ellers vil en null bli gitt.

Etter å ha trykket på OK, vil en ny kolonne med sikkerhetsmerker legges til i tabellen.

Klikk på rullegardinknappen i Sikkerhetsticker-kolonnen og fjern merket for null for å fjerne alle nullverdier i kolonnen.

Etter det vil du sitte igjen med all obligasjonsinformasjon fra alle arkene. Alt du trenger å gjøre er å gjenta de utførte transformasjonene i Bond(2)-spørringen og bruke dem på de nestede tabellene i Data-kolonnen.

Opprette en egendefinert kolonne for obligasjonsspørring

Gå til Bond(2)-spørringen, åpne Advanced Editor-vinduet og kopier følgende kode:

Gå deretter tilbake til Bond-spørringen og lag en annen egendefinert kolonne. Siden du må bruke flere transformasjoner over flere trinn, må du bruke en let- setning. Så skriv inn la og lim inn koden fra Advanced Editor.

Deretter endrer du Bond_Sheet til Data for å transformere tabellen i Data-kolonnen.

Etter å ha trykket på OK vil en ny kolonne bli lagt til i tabellen.

Hvis du klikker på det hvite rommet inne i en celle i den kolonnen, vil du se dataene fra Bond(2)-spørringen.

Alt du trenger er i de to siste kolonnene i Bond-søket. Så velg både Security Ticker og Custom-kolonnene og klikk Fjern andre kolonner fra Fjern kolonner-båndet.

Utvid Egendefinert-kolonnen og fjern merket for Bruk det opprinnelige navnet som prefiks.

Når du er ferdig, valider tabellen. Under tabellen velger du Kolonneprofilering basert på hele datasettet .

Etter den transformasjonen vil det oppstå en feil i Data-kolonnen.

Så undersøk feilen ved å klikke på Data-kolonnen og velge Keep Errors i Keep Rows-båndet.

Hvis du klikker på feilverdien i Data-kolonnen, kan du lese feilmeldingen nedenfor:

For å løse problemene, fjern først trinnet Beholdte feil. Klikk på Data-kolonnen og velg Fjern feil fra Fjern rader-båndet.

Sett deretter kolonneprofileringsalternativet tilbake til de 1000 øverste radene. Og det er det!

Konklusjon

Denne brukergrensesnittdrevne løsningen kan hjelpe deg med å legge til flere ark fra en Excel-fil til LuckyTemplates. I stedet for å lage 60 separate spørringer og utføre alle transformasjoner gjentatte ganger, kan denne løsningen lage en enkelt spørring som utfører alle transformasjonene. Utnytt og maksimer denne løsningen til .

Melissa