Soms zijn er situaties waarbij vooraf niet precies bekend is hoeveel en welke rijen uit de brongegevens geïmporteerd moeten worden. Stel dat we gegevens uit een tekstbestand in Power Query moeten laden, wat op het eerste gezicht geen groot probleem is. De moeilijkheid is dat het bestand regelmatig wordt bijgewerkt en dat het morgen een ander aantal regels met gegevens kan hebben, een koptekst van drie, niet twee regels, enz.:
Dat wil zeggen, we kunnen vooraf niet met zekerheid zeggen vanaf welke regel en hoeveel regels er precies geïmporteerd moeten worden. En dit is een probleem, omdat deze parameters hard gecodeerd zijn in de M-code van het verzoek. En als u een aanvraag doet voor het eerste bestand (5 regels importeren vanaf de 4e), dan zal het niet meer correct werken met de tweede.
Het zou geweldig zijn als onze query zelf het begin en einde van het "zwevende" tekstblok voor import zou kunnen bepalen.
De oplossing die ik wil voorstellen, is gebaseerd op het idee dat onze gegevens enkele trefwoorden of waarden bevatten die kunnen worden gebruikt als markeringen (functies) van het begin en einde van het gegevensblok dat we nodig hebben. In ons voorbeeld is het begin een regel die begint met het woord SKU, en het einde is een regel met het woord Totaal. Deze rijvalidatie is eenvoudig te implementeren in Power Query met behulp van een voorwaardelijke kolom - een analoog van de functie IF (ALS) вMicrosoft Excel.
Laten we eens kijken hoe we het moeten doen.
Laten we eerst de inhoud van ons tekstbestand op de standaard manier in Power Query laden - via de opdracht Gegevens – Gegevens ophalen – Uit bestand – Uit tekst/CSV-bestand (Data – Gegevens ophalen – Van bestand – Van tekst/CSV-bestand). Als u Power Query als aparte invoegtoepassing hebt geïnstalleerd, staan de bijbehorende opdrachten op het tabblad Power Query:
Zoals altijd kunt u bij het importeren het kolomscheidingsteken selecteren (in ons geval is dit een tabblad), en na het importeren kunt u de automatisch toegevoegde stap verwijderen gewijzigd type: (Gewijzigd type), omdat het voor ons te vroeg is om gegevenstypen aan kolommen toe te wijzen:
Nu met het commando Een kolom toevoegen – Voorwaardelijke kolom (Kolom toevoegen — Voorwaardelijke kolom)laten we een kolom toevoegen met het aanvinken van twee voorwaarden - aan het begin en het einde van het blok - en waarbij in elk geval verschillende waarden worden weergegeven (bijvoorbeeld getallen 1 и 2). Als aan geen van de voorwaarden is voldaan, wordt output nul:
Na het klikken op OK we krijgen het volgende beeld:
Laten we nu naar het tabblad gaan. Transformatie en kies een team Vullen – Omlaag (Transformeren – Vullen – Omlaag) - onze enen en tweeën strekken zich uit in de kolom:
Welnu, zoals je misschien al vermoedt, kun je eenvoudig de eenheden in de voorwaardelijke kolom filteren - en hier is ons felbegeerde stukje gegevens:
Het enige dat overblijft is om de eerste regel naar de koptekst te verhogen met het commando Gebruik de eerste regel als koptekst tab Home (Home - Gebruik de eerste rij als kopteksten) en verwijder de onnodige meer voorwaardelijke kolom door met de rechtermuisknop op de kop te klikken en de opdracht te selecteren Kolom verwijderen (Kolom verwijderen):
Probleem opgelost. Wanneer nu de gegevens in het brontekstbestand worden gewijzigd, bepaalt de query nu onafhankelijk het begin en einde van het "zwevende" fragment van de gegevens die we nodig hebben en importeert elke keer het juiste aantal regels. Natuurlijk werkt deze aanpak ook bij het importeren van XLSX-, niet TXT-bestanden, en bij het in één keer importeren van alle bestanden uit een map met het commando Gegevens – Gegevens ophalen – Uit bestand – Uit map (Gegevens — Gegevens ophalen — Uit bestand — Uit map).
- Tabellen samenstellen uit verschillende bestanden met Power Query
- Een kruistabel opnieuw ontwerpen naar plat met macro's en Power Query
- Een project-Gantt-diagram maken in Power Query