Meerdere bladen in Excel toevoegen aan LuckyTemplates

In deze zelfstudie wordt besproken hoe u een Excel-bestand met meerdere bladen naar één LuckyTemplates-tabel kunt importeren en openen.

Dit Excel-gegevensbestand wordt gebruikt voor deze zelfstudie.

Het bestand bevat 60 gegevensbladen met dezelfde lay-out.

Het doel van deze zelfstudie is om alle Excel-bladen in LuckyTemplates te laden en de Bond-informatie als één tabel op te halen. U moet de Security-ticker ophalen en die in een aparte kolom toevoegen. Voeg ze vervolgens allemaal samen toe in een enkele tabel die gebruikmaakt van de gebruikersinterface om de code te genereren die u nodig hebt.

Inhoudsopgave

• De Excel-bladen importeren naar LuckyTemplates
• De beveiligingsticker van Excel naar LuckyTemplates brengen
• Lege rijen verwijderen
• Een query dupliceren
• Gegevenstypen toewijzen aan kolommen
• Beveiligingtickerkolom toevoegen van Excel naar LuckyTemplates
• Een aangepaste kolom maken voor obligatiequery
• Conclusie

De Excel-bladen importeren naar LuckyTemplates

Het eerste dat u moet doen, is de Power Query-editor openen en een parameter maken om de bestandslocatie op te slaan met behulp van de optie Parameters beheren .

Klik in het vak Parameters beheren op Nieuw en voer FileLocation in als de parameternaam. Stel vervolgens het gegevenstype in op tekst en voorgestelde waarden op elke waarde . Kopieer het bestandspad van het Excel-bestand en plak het in de velden Huidige waarde.

Nadat u op OK hebt gedrukt, klikt u op Nieuwe bron en selecteert u Excel-werkmap .

Selecteer en open vervolgens het Excel-bestand. Als u klaar bent, wordt het Navigator-scherm geopend en worden alle bladen in het Excel-bestand weergegeven.

Selecteer het eerste vel en druk op OK. Daarna ziet u de tabel in de Power Query-editor.

Het volgende dat u moet doen, is het hardgecodeerde bestandspad wijzigen in de bestandsparameter. Open het Advanced Editor-venster en wijzig het bestandspad in de broncode in FileLocation .

Het is het beste om uw gegevens in Excel-tabellen te hebben, omdat de grenzen of het gegevensbereik worden gedefinieerd voordat u ze in Power Query brengt . Omdat het bestand bladen en geen tabellen bevat, bestaat het risico dat er lege kolommen en lege rijen worden toegevoegd, dus u moet dat uitzoeken.

De beveiligingsticker van Excel naar LuckyTemplates brengen

Wetende dat de lay-out van de bladen vast is, helpt bij het bouwen van een oplossing, vooral als u de gebruikersinterface wilt maximaliseren en gebruiken om de code te genereren. Een vereiste is bijvoorbeeld om een ​​kolom toe te voegen die de Security-ticker bevat. Als u naar de gegevens kijkt, ziet u de ticker.

De gebruikersinterface kan u helpen de waarde van de ticker te extraheren. Klik met de rechtermuisknop op de cel en selecteer Toevoegen als nieuwe query .

In de formulebalk ziet u dat de tabelnaam wordt gevolgd door een op nul gebaseerd rijnummer tussen accolades. De accolades worden positionele indexoperatoren genoemd . U ziet ook de veldnaam tussen vierkante haken die veldtoegangsoperatoren worden genoemd .

Met die codes kunt u nu de waarde extraheren. Ga terug naar de Bond-tabel en verkrijg de Bond-gegevens. Verwijder eerst de bovenste 8 acht rijen. Klik op Rijen verwijderen en selecteer Bovenste rijen verwijderen .

Voer vervolgens 8 in het veld Aantal rijen in en druk op OK.

Klik vervolgens op Eerste rij als kopteksten gebruiken om de kopteksten in te stellen.

Als u klaar bent, ziet de Bond-tabel er nu zo uit.

Direct onder de koptekst ziet u een dunne lijn die de kolomkwaliteit weergeeft. Vanaf daar kunt u zien dat er nogal wat lege plekken in de kolommen zijn. Dit betekent dat het bestand veel lege rijen heeft opgeleverd.

Lege rijen verwijderen

Om de lege rijen te verwijderen, klikt u op Rijen verwijderen en selecteert u Lege rijen verwijderen .

Die transformatie genereert deze syntaxis:

Record.FieldValues ​​krijgt alle waarden uit de huidige rij in de tabel als een lijst. List.RemoveMatchingItems verwijdert alle waarden in de eerste lijst die overeenkomen met de tweede lijst. De tweede lijst bevat alleen een lege tekenreeks of een null. Dit zijn de waarden die worden uitgesloten van de eerste lijst.

Als alle lege tekenreeksen en null zijn verwijderd uit de lijst met de recordveldwaarden, zou de lijst leeg moeten zijn en zal List.IsEmpty evalueren naar True. Vervolgens behoudt Table.SelectRows de Trues.

Je moet niet eindigen met een tabel met alleen maar lege rijen. Daarom wordt het sleutelwoord not toegevoegd vóór de List.IsEmpty . Dat retourneert vervolgens een tabel met niet-lege rijen.

Naast lege rijen, moet u ook lege kolommen verwijderen. Maar kijk eerst wat Power Query genereert wanneer u een kolom verwijdert. Selecteer de vierde kolom en klik op Kolommen verwijderen .

Na het uitvoeren van de transformatie ziet u deze syntaxis in de formulebalk.

Het roept de functie Table.RemoveColumns aan , verwijst vervolgens naar en geeft de vorige stap in het deelvenster Toegepaste stappen door als eerste argument. De transformatie geeft verder een lijst door met de kolomnaam van de kolommen die u wilt verwijderen.

Een query dupliceren

Dupliceer nu de query en selecteer de stap Kopteksten promoten in het deelvenster Toegepaste stappen. Klik vervolgens met de rechtermuisknop op die stap en selecteer Verwijderen tot einde .

Onthoud dat u de positionele indexoperator kunt gebruiken om een ​​op nul gebaseerd rijnummer tussen accolades door te geven. Bewaar dus alleen de eerste rij door 0 in te voeren tussen twee accolades in de formulebalk.

Als je klaar bent, ziet de Bond-tafel er zo uit.

Hergebruik van daaruit een deel van de logica die is gemaakt door de stap Verwijderde lege rijen om de record om te zetten in een lijst en om de null te verwijderen. Ga terug naar de obligatiequery en selecteer de stap Lege rijen verwijderen. Kopieer vervolgens deze M-code.

Ga terug naar de gedupliceerde query en plak de code in de formulebalk. Rangschik vervolgens enkele strings om de formule er zo uit te laten zien.

Je hebt nu een lijst gemaakt met kolomnamen die je wilt behouden. Hernoem de query Kolomnamen .

Ga dan terug naar de Bond-query. Aangezien u een query hebt gemaakt die alle kolommen bevat die u wilt behouden, moet u alle kolommen die u wilt uitsluiten vermelden in de functie Table.RemoveColumns .

Wijzig {Column4} met dezelfde syntaxis die is gekopieerd uit de stappen Verwijderde lege rijen. Wijzig ook {“”, null} in ColumnNames .

Vervolgens moet u een lijst doorgeven met de daadwerkelijke kolomnamen uit de Bond-tabel. Wijzig de Record.FieldValues(_) in Table.ColumnNames() . Voer #"Verwijderde lege rijen" tussen haakjes in om een ​​tabelreferentieargument door te geven.

Gegevenstypen toewijzen aan kolommen

Het volgende dat u moet doen, is de juiste gegevenstypen toewijzen aan de kolommen. Klik voor de kolom Datum op het pictogram naast de koptekst en selecteer Datum.

Selecteer voor de kolommen PX_LAST en YLD_YTM_MID het gegevenstype Decimal Number.

Met deze drie query's hebt u alle bouwstenen gemaakt die u kunt gebruiken om een ​​oplossing te ontwerpen die alle bladen in het Excel- bestand adresseert. Daarvoor moet u de Bond-query dupliceren en alle stappen behalve de Bronstap in het deelvenster Toegepaste stappen verwijderen.

Beveiligingtickerkolom toevoegen van Excel naar LuckyTemplates

In de stap Bron ziet u alle gegevens in het Excel-bestand. In plaats van een aparte query voor elk blad te maken, kunt u de Bond-query gebruiken en de geneste tabellen in de kolom Gegevens transformeren.

Voeg eerst de Security-ticker toe. Als u op de witte ruimte in de cel van een tabel klikt, ziet u een voorbeeld van de inhoud van die tabel.

U moet een logica maken met behulp van een instructie om de Security-ticker te krijgen. Ga naar de Column2-query en kopieer het adres van de Security-ticker uit de formulebalk.

Ga vervolgens terug naar de Bond-query en voeg een aangepaste kolom toe.

Noem de kolom Security Ticker en schrijf de volgende M-code.

De formule heeft een IF- instructie die zegt dat als het woord Beveiliging wordt gevonden in Kolom1, dit de waarde zal geven van de cel uit Kolom2 die ernaast staat. Anders wordt een null gegeven.

Nadat u op OK hebt gedrukt, wordt een nieuwe kolom met de Security-tickers aan de tabel toegevoegd.

Klik op de vervolgkeuzeknop van de kolom Security Ticker en deselecteer null om alle null-waarden in de kolom te verwijderen.

Daarna blijft u achter met alle informatie over de obligaties van alle bladen. Het enige dat u hoeft te doen, is de uitgevoerde transformaties herhalen die in de Bond(2)-query zijn uitgevoerd en deze toepassen op de geneste tabellen in de kolom Gegevens.

Een aangepaste kolom maken voor obligatiequery

Ga naar de Bond(2)-query, open het Advanced Editor-venster en kopieer de volgende code:

Ga vervolgens terug naar de Bond-query en maak nog een aangepaste kolom. Aangezien u meerdere transformaties in meerdere stappen moet toepassen, moet u een let- instructie gebruiken. Dus, voer let in en plak de code uit de geavanceerde editor.

Wijzig vervolgens de Bond_Sheet in Data om de tabel in de kolom Data te transformeren.

Nadat u op OK heeft gedrukt, wordt een nieuwe kolom in de tabel toegevoegd.

Als u op de witte ruimte in een willekeurige cel van die kolom klikt, ziet u de gegevens van de Bond(2)-query.

Alles wat u nodig heeft, staat in de laatste twee kolommen van de Bond-query. Selecteer dus zowel de beveiligingsticker als de aangepaste kolommen en klik op Andere kolommen verwijderen op het lint Kolommen verwijderen.

Vouw de kolom Aangepast uit en schakel het selectievakje Gebruik de oorspronkelijke naam als voorvoegsel uit.

Als u klaar bent, valideert u de tabel. Selecteer onder de tabel de optie Kolomprofilering op basis van volledige gegevensset .

Na die transformatie treedt er een fout op in de kolom Gegevens.

Onderzoek dus de fout door op de kolom Gegevens te klikken en Fouten behouden te selecteren in het lint Bewaar rijen.

Als u op de foutwaarde in de kolom Gegevens klikt, kunt u het onderstaande foutbericht lezen:

Om de problemen op te lossen, verwijdert u eerst de stap Bewaarde fouten. Klik op de kolom Gegevens en selecteer Fouten verwijderen op het lint Verwijder rijen.

Stel vervolgens de kolomprofileringsoptie terug in op de bovenste 1000 rijen. En dat is het!

Conclusie

Deze door de gebruikersinterface aangestuurde oplossing kan u helpen om meerdere bladen uit een Excel-bestand toe te voegen aan LuckyTemplates. In plaats van 60 afzonderlijke query's te maken en alle transformaties herhaaldelijk uit te voeren, kan deze oplossing één query maken die alle transformaties uitvoert. Gebruik en maximaliseer deze oplossing om een ​​geweldig gegevensrapport op te bouwen .

Melissa