Lägga till flera ark i Excel till LuckyTemplates

Denna handledning kommer att diskutera hur man importerar och öppnar en Excel-fil med flera ark till en LuckyTemplates-tabell.

Denna data Excel-fil kommer att användas för denna handledning.

Filen innehåller 60 datablad med samma layout.

Syftet med denna handledning är att ladda alla Excel-ark till LuckyTemplates och hämta Bond-informationen som en tabell. Du måste få säkerhetstickern och lägga till den i en separat kolumn. Lägg sedan till dem alla tillsammans i en enda tabell som utnyttjar användargränssnittet för att generera koden du behöver.

Innehållsförteckning

• Importera Excel-ark till LuckyTemplates
• Ta fram säkerhetstickern från Excel till LuckyTemplates
• Ta bort tomma rader
• Duplicera en fråga
• Tilldela datatyper till kolumner
• Lägga till säkerhetssymbolkolumn från Excel till LuckyTemplates
• Skapa en anpassad kolumn för obligationsfråga
• Slutsats

Importera Excel-ark till LuckyTemplates

Det första du ska göra är att öppna och skapa en parameter för att lagra filplatsen med alternativet Hantera parametrar .

I rutan Hantera parametrar klickar du på Ny och anger FileLocation som parameternamn. Ställ sedan in datatyp på text och föreslagna värden till valfritt värde . Kopiera filsökvägen till Excel-filen och klistra in den i fälten Aktuellt värde.

När du har tryckt på OK klickar du på Ny källa och väljer Excel-arbetsbok .

Välj sedan och öppna Excel-filen. När det är klart öppnas Navigator-skärmen och listar alla ark i Excel-filen.

Välj det första arket och tryck på OK. Efter det kommer du att se tabellen i Power Query Editor.

Nästa sak att göra är att ändra den hårdkodade filsökvägen till filparametern. Öppna fönstret Advanced Editor och ändra filsökvägen i källkoden till FileLocation .

Det är bäst att ha dina data i Excel-tabeller eftersom gränserna eller dataintervallet definieras innan du tar in dem i. Eftersom filen innehåller ark och inga tabeller, riskerar den att ta in tomma kolumner och tomma rader så du måste reda ut det.

Ta fram säkerhetstickern från Excel till LuckyTemplates

Att veta att layouten på arken är fixad hjälper till att bygga en lösning, speciellt om du vill maximera och använda användargränssnittet för att generera koden. Ett krav är till exempel att lägga till en kolumn som innehåller säkerhetstickern. Om du tittar på uppgifterna kan du se tickern.

Användargränssnittet kan hjälpa dig att extrahera värdet av tickern. Högerklicka på cellen och välj Lägg till som ny fråga .

I formelfältet ser du att tabellnamnet följs av ett nollbaserat radnummer mellan hakparenteser. De krulliga parenteserna kallas positionsindexoperatorer . Du kommer också att se fältnamnet omgivet av hakparenteser som kallas fältåtkomstoperatorer .

Med dessa koder kan du nu extrahera värdet. Gå tillbaka till Bond-tabellen och hämta Obligationsdata. Ta först bort de 8 översta åtta raderna. Klicka på Ta bort rader och välj Ta bort översta rader .

Ange sedan 8 i fältet Antal rader och tryck på OK.

Klicka sedan på Använd första raden som rubriker för att ställa in rubrikerna.

När det är klart kommer Bond-bordet nu att se ut så här.

Direkt under rubriken ser du en tunn linje som representerar kolumnkvaliteten. Därifrån kan du se att det finns ganska många tomrum i kolumnerna. Det betyder att filen fick in många tomma rader.

Ta bort tomma rader

För att ta bort de tomma raderna, klicka på Ta bort rader och välj Ta bort tomma rader .

Den transformationen genererar denna syntax:

Record.FieldValues ​​hämtar alla värden från den aktuella raden i tabellen som en lista. List.RemoveMatchingItems tar bort alla värden i den första listan som har en matchning i den andra listan. Den andra listan innehåller bara en tom textsträng eller en null. Dessa är värdena som kommer att exkluderas från den första listan.

Om alla tomma textsträngar och null har tagits bort från listan med postfältvärdena, bör listan vara tom och List.IsEmpty kommer att utvärderas till True. Sedan kommer Table.SelectRows att behålla Trues.

Du bör inte sluta med en tabell med bara tomma rader. Det är därför nyckelordet inte läggs till före List.IsEmpty . Det returnerar sedan en tabell som innehåller icke-tomma rader.

Bortsett från tomma rader måste du också ta bort tomma kolumner. Men innan dess, ta en titt på vad Power Query genererar när du tar bort en kolumn. Välj den fjärde kolumnen och klicka på Ta bort kolumner .

När du har utfört omvandlingen ser du denna syntax i formelfältet.

Den anropar funktionen Table.RemoveColumns och refererar sedan till och skickar det föregående steget i rutan Tillämpade steg som första argument. Transformationen skickar vidare en lista som innehåller kolumnnamnet på de kolumner som du vill ta bort.

Duplicera en fråga

Duplicera nu frågan och välj steget Marknadsför rubriker i rutan Tillämpade steg. Högerklicka sedan på det steget och välj Ta bort till slut .

Kom ihåg att du kan använda positionsindexoperatorn för att skicka ett nollbaserat radnummer i en uppsättning parenteser. Så behåll bara den första raden genom att mata in 0 inom två parenteser i formelfältet.

När det är klart kommer Bond-bordet att se ut så här.

Därifrån återanvänder du en del av logiken som skapades av steget Borttagna tomma rader för att förvandla posten till en lista och för att ta bort nollvärdet. Gå tillbaka till Bond-frågan och välj steget Ta bort tomma rader. Kopiera sedan denna M-kod.

Gå tillbaka till den dubblerade frågan och klistra in koden i formelfältet. Ordna sedan några strängar för att få formeln att se ut så här.

Du har nu skapat en lista med kolumnnamn som du vill behålla. Byt namn på frågan Kolumnnamn .

Gå sedan tillbaka till Bond-frågan. Eftersom du har skapat en fråga som innehåller alla kolumner du vill behålla, måste du lista alla kolumner som du vill utesluta i funktionen Table.RemoveColumns .

Ändra {Column4} med samma syntax som kopierades från stegen Removed Blank Rows. Ändra även {“”, null} till ColumnNames .

Sedan måste du skicka en lista med de faktiska kolumnnamnen från Bond-tabellen. Ändra Record.FieldValues(_) till Table.ColumnNames() . Mata in #”Removed Blank Rows” inom parentesen för att skicka ett tabellreferensargument.

Tilldela datatyper till kolumner

Nästa sak att göra är att tilldela lämpliga datatyper till kolumnerna. För kolumnen Datum klickar du på ikonen bredvid rubriken och väljer Datum.

För kolumnerna PX_LAST och YLD_YTM_MID väljer du datatypen decimaltal.

Med dessa tre frågor har du skapat alla byggstenar som du kan använda för att designa en lösning som adresserar alla ark i filen. För det måste du duplicera Bond-frågan och ta bort alla steg utom källsteget i rutan Tillämpade steg.

Lägga till säkerhetssymbolkolumn från Excel till LuckyTemplates

I källsteget kan du se all data i Excel-filen. Istället för att skapa en separat fråga för varje blad kan du använda Bond-frågan och transformera de kapslade tabellerna i kolumnen Data.

Lägg först till säkerhetstickern. Om du klickar på det vita utrymmet inuti cellen i en tabell, kommer du att se en förhandsvisning av innehållet i den tabellen.

Du måste skapa en logik med hjälp av ett uttalande för att få säkerhetstickern. Gå till kolumn2-frågan och kopiera adressen till säkerhetstickern från formelfältet.

Gå sedan tillbaka till Bond-frågan och lägg till en anpassad kolumn.

Namnge kolumnen Security Ticker och skriv följande M-kod.

Formeln har en IF -sats som säger att om ordet Säkerhet finns i Kolumn1, kommer det att ge värdet på cellen från Kolumn2 som ligger intill den. Annars kommer en noll att tillhandahållas.

Efter att ha tryckt på OK kommer en ny kolumn med säkerhetstickers att läggas till i tabellen.

Klicka på rullgardinsknappen i kolumnen Säkerhetsticker och avmarkera null för att ta bort alla nollvärden i kolumnen.

Efter det kommer du att lämnas med all information om obligationerna från alla ark. Allt du behöver göra är att upprepa de utförda transformationerna i Bond(2)-frågan och tillämpa dem på de kapslade tabellerna i kolumnen Data.

Skapa en anpassad kolumn för obligationsfråga

Gå till Bond(2)-frågan, öppna fönstret Advanced Editor och kopiera följande kod:

Gå sedan tillbaka till Bond-frågan och skapa en annan anpassad kolumn. Eftersom du behöver tillämpa flera transformationer över flera steg måste du använda en let- sats. Så, mata in låt och klistra in koden från Advanced Editor.

Ändra sedan Bond_Sheet till Data för att transformera tabellen i kolumnen Data.

Efter att ha tryckt på OK läggs en ny kolumn till i tabellen.

Om du klickar på det vita utrymmet inuti någon cell i den kolumnen kommer du att se data från Bond(2)-frågan.

Allt du behöver är i de två sista kolumnerna i Bond-frågan. Så, välj både Säkerhetsticker och Anpassad kolumner och klicka på Ta bort andra kolumner från menyfliksområdet Ta bort kolumner.

Expandera kolumnen Anpassad och avmarkera rutan Använd originalnamnet som prefix.

När du är klar, validera tabellen. Under tabellen väljer du alternativet Kolumnprofilering baserad på hela datamängden .

Efter den omvandlingen kommer ett fel att uppstå i kolumnen Data.

Så undersök felet genom att klicka på kolumnen Data och välja Behåll fel i menyfliksområdet Behåll rader.

Om du klickar på felvärdet i kolumnen Data kan du läsa felmeddelandet nedan:

För att lösa problemen, ta först bort steget Bevarade fel. Klicka på kolumnen Data och välj Ta bort fel från menyfliksområdet Ta bort rader.

Ställ sedan tillbaka kolumnprofileringsalternativet till de 1 000 översta raderna. Och det är allt!

Slutsats

Denna användargränssnittsdrivna lösning kan hjälpa dig att lägga till flera ark från en Excel-fil till LuckyTemplates. Istället för att skapa 60 separata frågor och utföra alla transformationer upprepade gånger, kan den här lösningen skapa en enda fråga som utför alla transformationer. Använd och maximera denna lösning till .

Melissa