Tilføjelse af flere ark i Excel til LuckyTemplates

Denne vejledning vil diskutere, hvordan man importerer og åbner en Excel-fil med flere ark til en LuckyTemplates-tabel.

Denne data Excel-fil vil blive brugt til denne øvelse.

Filen indeholder 60 ark data med samme layout.

Formålet med denne vejledning er at indlæse alle Excel-ark til LuckyTemplates og hente Bond-oplysningerne som én tabel. Du skal hente sikkerhedstikken og tilføje den i en separat kolonne. Føj derefter dem alle sammen i en enkelt tabel, der udnytter brugergrænsefladen til at generere den kode, du har brug for.

Indholdsfortegnelse

• Import af Excel-ark til LuckyTemplates
• Frembringelse af sikkerhedssymbolet fra Excel til LuckyTemplates
• Fjernelse af tomme rækker
• Duplikere en forespørgsel
• Tildeling af datatyper til kolonner
• Tilføjelse af sikkerhedssymbolkolonne fra Excel til LuckyTemplates
• Oprettelse af en brugerdefineret kolonne til obligationsforespørgsel
• Konklusion

Import af Excel-ark til LuckyTemplates

Den første ting at gøre er at åbne og oprette en parameter til at gemme filplaceringen ved at bruge indstillingen Administrer parametre .

Klik på Ny i feltet Administrer parametre, og indtast Filplacering som parameternavn. Indstil derefter datatypen til tekst og foreslåede værdier til enhver værdi . Kopier filstien til Excel-filen, og indsæt den i felterne Aktuel værdi.

Når du har trykket på OK, skal du klikke på Ny kilde og vælge Excel-projektmappe .

Vælg og åbn derefter Excel-filen. Når det er gjort, åbnes Navigator-skærmen og viser alle arkene i Excel-filen.

Vælg det første ark, og tryk på OK. Derefter vil du se tabellen i Power Query Editor.

Den næste ting at gøre er at ændre den hårdkodede filsti til filparameteren. Åbn vinduet Advanced Editor, og skift filstien i kildekoden til FileLocation .

Det er bedst at have dine data i Excel-tabeller, fordi grænserne eller dataområdet er defineret, før du bringer dem ind i. Da filen indeholder ark og ingen tabeller, risikerer den at bringe tomme kolonner og tomme rækker ind, så du skal ordne det.

Frembringelse af sikkerhedssymbolet fra Excel til LuckyTemplates

At vide, at layoutet af arkene er fast, hjælper med at bygge en løsning, især hvis du vil maksimere og bruge brugergrænsefladen til at generere koden. For eksempel er et krav at tilføje en kolonne, der indeholder sikkerhedssymbolet. Hvis du ser på dataene, kan du se tickeren.

Brugergrænsefladen kan hjælpe dig med at udtrække værdien af ​​tickeren. Højreklik på cellen, og vælg Tilføj som ny forespørgsel .

I formellinjen kan du se, at tabelnavnet er efterfulgt af et nul-baseret rækkenummer mellem krøllede parenteser. De krøllede parenteser kaldes positionelle indeksoperatorer . Du vil også se feltnavnet omgivet af firkantede parenteser, som kaldes feltadgangsoperatorer .

Med disse koder kan du nu udtrække værdien. Gå tilbage til Bond-tabellen og få Obligationsdataene. Fjern først de øverste 8 otte rækker. Klik på Fjern rækker , og vælg Fjern øverste rækker .

Indtast derefter 8 i feltet Antal rækker og tryk på OK.

Klik derefter på Brug første række som overskrifter for at indstille overskrifterne.

Når det er gjort, vil Bond-bordet nu se sådan ud.

Lige under overskriften vil du se en tynd linje, der repræsenterer kolonnekvaliteten. Derfra kan du se, at der er en del tomme felter i kolonnerne. Det betyder, at filen indbragte en masse tomme rækker.

Fjernelse af tomme rækker

For at fjerne de tomme rækker skal du klikke på Fjern rækker og vælge Fjern tomme rækker .

Denne transformation genererer denne syntaks:

Record.FieldValues ​​henter alle værdierne fra den aktuelle række i tabellen som en liste. List.RemoveMatchingItems fjerner alle værdierne på den første liste, der matcher den anden liste. Den anden liste indeholder kun en tom tekststreng eller en null. Dette er de værdier, der vil blive udelukket fra den første liste.

Hvis alle de tomme tekststrenge og null er blevet fjernet fra listen med postfeltværdierne, skal listen være tom, og List.IsEmpty vil evalueres til True. Derefter vil Table.SelectRows beholde Trues.

Du bør ikke ende med en tabel med kun tomme rækker. Det er derfor, nøgleordet ikke tilføjes før List.IsEmpty . Det returnerer derefter en tabel, der indeholder ikke-tomme rækker.

Bortset fra tomme rækker skal du også fjerne tomme kolonner. Men før det, tag et kig på, hvad Power Query genererer, når du fjerner en kolonne. Vælg den fjerde kolonne, og klik på Fjern kolonner .

Efter at have udført transformationen, vil du se denne syntaks i formellinjen.

Den kalder funktionen Table.RemoveColumns og refererer derefter til og videregiver det forrige trin i ruden Anvendte trin som første argument. Transformationen videregiver en liste, der indeholder kolonnenavnet på de kolonner, du vil fjerne.

Duplikere en forespørgsel

Dupliker nu forespørgslen, og vælg trinnet Fremhæv overskrifter i ruden Anvendte trin. Højreklik derefter på det trin og vælg Slet indtil slut .

Husk, at du kan bruge positionsindeksoperatoren til at sende et nul-baseret rækkenummer i et sæt krøllede parenteser. Så behold kun den første række ved at indtaste 0 inden for to krøllede parenteser i formellinjen.

Når det er gjort, vil Bond-bordet se sådan ud.

Derfra skal du genbruge en del af logikken, der blev oprettet af trinnet Fjernede tomme rækker, for at gøre posten til en liste og fjerne null. Gå tilbage til Bond-forespørgslen, og vælg trinnet Fjern tomme rækker. Kopier derefter denne M-kode.

Gå tilbage til den duplikerede forespørgsel, og indsæt koden i formellinjen. Arranger derefter nogle strenge for at få formlen til at se sådan ud.

Du har nu oprettet en liste med kolonnenavne, som du vil beholde. Omdøb forespørgslen Kolonnenavne .

Gå derefter tilbage til Bond-forespørgslen. Da du har oprettet en forespørgsel, der indeholder alle de kolonner, du vil beholde, skal du angive alle kolonner, som du vil udelukke, i funktionen Table.RemoveColumns .

Skift {Kolonne4} med den samme syntaks, som blev kopieret fra trinnene Fjernede tomme rækker. Skift også {“”, null} til ColumnNames .

Derefter skal du sende en liste med de faktiske kolonnenavne fra Bond-tabellen. Skift Record.FieldValues(_) til Table.ColumnNames() . Indtast #”Removed Blank Rows” inde i parentesen for at sende et tabelreferenceargument.

Tildeling af datatyper til kolonner

Den næste ting at gøre er at tildele passende datatyper til kolonnerne. For kolonnen Dato skal du klikke på ikonet ved siden af ​​overskriften og vælge Dato.

For kolonnerne PX_LAST og YLD_YTM_MID skal du vælge datatypen Decimaltal.

Med disse tre forespørgsler har du oprettet alle de byggeklodser, du kan bruge til at designe en løsning, der adresserer alle arkene i filen. Til det skal du duplikere Bond-forespørgslen og slette alle trinene undtagen Kildetrinnet i ruden Anvendte trin.

Tilføjelse af sikkerhedssymbolkolonne fra Excel til LuckyTemplates

I kildetrinet kan du se alle data i Excel-filen. I stedet for at oprette en separat forespørgsel for hvert ark, kan du bruge Bond-forespørgslen og transformere de indlejrede tabeller i kolonnen Data.

Tilføj først sikkerhedssymbolet. Hvis du klikker på det hvide mellemrum inde i cellen i en tabel, vil du se en forhåndsvisning af indholdet af denne tabel.

Du skal oprette en logik ved hjælp af en erklæring for at få sikkerhedstickeren. Gå til kolonne 2-forespørgslen og kopier adressen på sikkerhedssymbolet fra formellinjen.

Gå derefter tilbage til Bond-forespørgslen og tilføj en brugerdefineret kolonne.

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

Formlen har en IF -sætning, der siger, at hvis ordet Sikkerhed findes i Kolonne1, vil det give værdien af ​​cellen fra Kolonne2, som støder op til den. Ellers vil et nul blive angivet.

Efter at have trykket på OK, vil en ny kolonne med sikkerhedssymbolerne blive tilføjet til tabellen.

Klik på rullemenuen i kolonnen Security Ticker, og fravælg null for at fjerne alle nuller i kolonnen.

Derefter vil du stå tilbage med alle obligationernes oplysninger fra alle arkene. Alt du skal gøre er at gentage de udførte transformationer i Bond(2)-forespørgslen og anvende dem på de indlejrede tabeller i kolonnen Data.

Oprettelse af en brugerdefineret kolonne til obligationsforespørgsel

Gå til Bond(2)-forespørgslen, åbn vinduet Advanced Editor, og kopier følgende kode:

Gå derefter tilbage til Bond-forespørgslen og opret en anden brugerdefineret kolonne. Da du skal anvende flere transformationer på tværs af flere trin, skal du bruge en let -sætning. Så indtast lad og indsæt koden fra den avancerede editor.

Skift derefter Bond_Sheet til Data for at transformere tabellen i kolonnen Data.

Efter at have trykket på OK, tilføjes en ny kolonne i tabellen.

Hvis du klikker på det hvide mellemrum inde i en hvilken som helst celle i den kolonne, vil du se dataene fra Bond(2)-forespørgslen.

Alt du behøver er i de sidste to kolonner i Bond-forespørgslen. Så vælg både sikkerhedstikken og brugerdefinerede kolonner, og klik på Fjern andre kolonner fra båndet Fjern kolonner.

Udvid kolonnen Brugerdefineret, og fjern markeringen i feltet Brug det oprindelige navn som præfiks.

Når du er færdig, valider tabellen. Under tabellen skal du vælge indstillingen Kolonneprofilering baseret på hele datasættet .

Efter denne transformation vil der opstå en fejl i kolonnen Data.

Så undersøg fejlen ved at klikke på kolonnen Data og vælge Behold fejl på båndet Behold rækker.

Hvis du klikker på fejlværdien i kolonnen Data, kan du læse fejlmeddelelsen nedenfor:

For at løse problemerne skal du først fjerne trinnet Beholdte fejl. Klik på kolonnen Data, og vælg Fjern fejl fra båndet Fjern rækker.

Indstil derefter kolonneprofileringsindstillingen tilbage til de øverste 1000 rækker. Og det er det!

Konklusion

Denne brugergrænseflade-drevne løsning kan hjælpe dig med at tilføje flere ark fra en Excel-fil til LuckyTemplates. I stedet for at oprette 60 separate forespørgsler og udføre alle transformationer gentagne gange, kan denne løsning oprette en enkelt forespørgsel, der udfører alle transformationerne. Udnyt og maksimer denne løsning til .

Melissa