Inhoud
Als u al bent begonnen met het gebruik van de tools van de gratis Power Query-invoegtoepassing in Microsoft Excel, zult u zeer binnenkort een zeer gespecialiseerd, maar zeer frequent en vervelend probleem tegenkomen dat verband houdt met het voortdurend verbreken van koppelingen naar brongegevens. De essentie van het probleem is dat als u in uw query naar externe bestanden of mappen verwijst, Power Query het absolute pad ernaar in de querytekst hardcodeert. Alles werkt prima op je computer, maar als je besluit een bestand met een verzoek naar je collega's te sturen, dan zullen ze teleurgesteld zijn, want. ze hebben een ander pad naar de brongegevens op hun computer, en onze query zal niet werken.
Wat te doen in zo'n situatie? Laten we dit geval in meer detail bekijken met het volgende voorbeeld.
Formulering van het probleem
Stel dat we in de map E:Verkooprapporten ligt het bestand Top 100 producten.xls, wat een upload is van onze bedrijfsdatabase of ERP-systeem (1C, SAP, enz.) Dit bestand bevat informatie over de meest populaire handelsartikelen en ziet er van binnen als volgt uit:
Het is waarschijnlijk meteen duidelijk dat het bijna onmogelijk is om er in Excel in deze vorm mee te werken: lege rijen door één met gegevens, samengevoegde cellen, extra kolommen, een header met meerdere niveaus, enz. Zal interfereren.
Daarom maken we naast dit bestand in dezelfde map nog een nieuw bestand Handler.xlsx, waarin we een Power Query-query maken die lelijke gegevens laadt uit het bronuploadbestand Top 100 producten.xls, en zet ze op volgorde:
Een verzoek indienen bij een extern bestand
Het bestand openen Handler.xlsx, selecteer op het tabblad Data commando Gegevens ophalen - uit bestand - uit Excel-werkmap (Gegevens — Gegevens ophalen — Uit bestand — Uit Excel), specificeer vervolgens de locatie van het bronbestand en het blad dat we nodig hebben. De geselecteerde gegevens worden in de Power Query-editor geladen:
Laten we ze weer normaal maken:
- Wis lege regels met Home — Regels verwijderen — Lege regels verwijderen (Home — Rijen verwijderen — Lege rijen verwijderen).
- Verwijder onnodige bovenste 4 regels door Home — Rijen verwijderen — Bovenste rijen verwijderen (Home — Rijen verwijderen — Bovenste rijen verwijderen).
- Verhoog de eerste rij naar de tabelkop met de knop Gebruik de eerste regel als koptekst tab Home (Home - Gebruik de eerste rij als koptekst).
- Scheid het vijfcijferige artikel van de productnaam in de tweede kolom met het commando kolom splitsen tab Transformatie (Transformeren — Kolom splitsen).
- Verwijder onnodige kolommen en hernoem de koppen van de overige kolommen voor een betere zichtbaarheid.
Als resultaat zouden we het volgende, veel aangenamer beeld moeten krijgen:
Het blijft om deze veredelde tabel terug te uploaden naar het blad in ons bestand Handler.xlsx het team sluiten en downloaden (Home — Sluiten&Laden) tab Home:
Het pad naar een bestand in een verzoek vinden
Laten we nu eens kijken hoe onze query er "onder de motorkap" uitziet in de interne taal die is ingebouwd in Power Query met de beknopte naam "M". Ga hiervoor terug naar onze vraag door erop te dubbelklikken in het rechterdeelvenster Verzoeken en connecties en op het tabblad Beoordeling kiezen Geavanceerde editor (Bekijken - Geavanceerde editor):
In het geopende venster onthult de tweede regel onmiddellijk een hardgecodeerd pad naar ons oorspronkelijke uploadbestand. Als we deze tekenreeks kunnen vervangen door een parameter, variabele of een link naar een Excel-bladcel waar dit pad vooraf is geschreven, kunnen we dit later gemakkelijk wijzigen.
Een slimme tabel met een bestandspad toevoegen
Laten we Power Query voor nu sluiten en terugkeren naar ons bestand Handler.xlsx. Laten we een nieuw leeg blad toevoegen en er een kleine "slimme" tabel op maken, in de enige cel waarvan het volledige pad naar ons brongegevensbestand zal worden geschreven:
Om een slimme tabel van een normaal bereik te maken, kun je de sneltoets gebruiken Ctrl+T of knop Opmaken als een tabel tab Home (Home - Opmaken als tabel). De kolomkop (cel A1) kan van alles zijn. Merk ook op dat ik de tabel voor de duidelijkheid een naam heb gegeven parameters tab Constructeur (Ontwerp).
Een pad kopiëren vanuit Verkenner of zelfs handmatig invoeren is natuurlijk niet bijzonder moeilijk, maar het is het beste om de menselijke factor te minimaliseren en het pad, indien mogelijk, automatisch te bepalen. Dit kan worden geïmplementeerd met behulp van de standaard Excel-werkbladfunctie CELL (CEL), die een heleboel nuttige informatie kan geven over de cel die als argument is opgegeven, inclusief het pad naar het huidige bestand:
Als we aannemen dat het brongegevensbestand altijd in dezelfde map staat als onze Processor, dan kan het pad dat we nodig hebben gevormd worden door de volgende formule:
=LEFT(CELL(“bestandsnaam”);FIND(“[“;CELL(“bestandsnaam”))-1)&”Top 100 producten.xls”
of in Engelse versie:
=LEFT(CELL(«bestandsnaam»);FIND(«[«;CELL(«bestandsnaam»))-1)&»Топ-100 товаров.xls»
… waar is de functie LEVSIMV (LINKS) neemt een stuk tekst van de volledige link naar de opening vierkante haak (dwz het pad naar de huidige map), en dan wordt de naam en extensie van ons brongegevensbestand eraan vastgelijmd.
Parameteriseer het pad in de query
De laatste en belangrijkste aanraking blijft - om het pad naar het bronbestand in het verzoek te schrijven Top 100 producten.xls, verwijzend naar cel A2 van onze gemaakte "slimme" tabel parameters.
Laten we hiervoor teruggaan naar de Power Query-query en deze opnieuw openen Geavanceerde editor tab Beoordeling (Bekijken - Geavanceerde editor). In plaats van een tekenreeks-pad tussen aanhalingstekens “E:VerkooprapportenTop 100 producten.xlsx” Laten we de volgende structuur introduceren:
Excel.CurrentWorkbook(){[Naam=”Instellingen”]}[Inhoud]0 {}[Pad naar brongegevens]
Laten we eens kijken waar het uit bestaat:
- Excel.CurrentWorkbook() is een functie van de M-taal voor toegang tot de inhoud van het huidige bestand
- {[Naam=”Instellingen”]}[Inhoud] - dit is een verfijningsparameter voor de vorige functie, die aangeeft dat we de inhoud van de "slimme" tabel willen krijgen parameters
- [Pad naar brongegevens] is de naam van de kolom in de tabel parameterswaarnaar we verwijzen
- 0 {} is het rijnummer in de tabel parameterswaarvan we gegevens willen overnemen. De dop telt niet en de nummering begint bij nul, niet bij één.
Dat is alles, in feite.
Het blijft om op te klikken Finish en bekijk hoe ons verzoek werkt. Wanneer nu de volledige map met beide bestanden naar een andere pc wordt verzonden, blijft het verzoek operationeel en bepaalt het automatisch het pad naar de gegevens.
- Wat is Power Query en waarom is het nodig bij het werken in Microsoft Excel?
- Een zwevend tekstfragment importeren in Power Query
- Een XNUMXD-kruistabel opnieuw ontwerpen naar een platte tabel met Power Query