Inhoudsopgave maken met behulp van Power Query in Excel

[Bobbje]

Mijn vraag
Naar een klant toe willen we vast een gedeelte van een opdracht opleveren volgens een voorgegeschreven oplever dossier. Dit is een zip bestandje met daarin mapjes volgens een standaard opbouw. In deze mapjes kunnen bestanden zitten of nog leeg zijn. Maar de klant wil wel weten welke folder ook bewust leeg zijn gelaten.

Daarom moet een inhoudsopgave worden gemaakt met wat in het oplever dossier zit. Collega's stoppen de documenten die worden opgeleverd in het goede folder op een netwerkschijf. Op basis daarvan zou dus de inhoudsopgave makkelijk gemaakt kunnen worden met behulp van een Power Query.

Relevante software en hardware die ik gebruik
Excel Power Query

Wat ik al gevonden of geprobeerd heb
Het lukt mij alwel om alle bestanden, in de folder en sub-folders, te krijgen inclusief de folder locatie via Power Query door een map te selecteren. Via de command: "Folder.Files"
Maar ik zou eigenlijk ook alle sub-folder mee willen krijgen in die lijst.
Het lukt mij wel om alle sub-folder te krijgen van het Hoofdfolder door "Folder.Contents" te gebruiken. Alleen deze geeft niet weer wat er weer in die sub-fodler zit, wat ik wel wil weten. Dus eigenlijk zou de laastse query weer moeten worden uitgevoerd op de "folder" resultaten.
Dus eigenlijk gewoon wat je in verkenner ziet alleen dan als een lijst in Excel.

Omdat het intotaal zo'n 250 sub-folder zijn, is handmatig een beetje veel werk welke makkelijker op te lossen moet zijn.

Voorbeeld waar in naar toe wil:

Hoofdfolder 0
-- Doc 0 - 01
-- Doc 0 - 02
-- Sub-Folder 1
---- Doc 1 - 01
---- Doc 1 - 02
---- Sub-Folder 1.1
------ Deze folder is nog leeg
---- Sub-Folder 1.2
------ Doc.1.2 - 01
-- Sub-folder 2
---- Doc 2 - 01
Enz.


Ik hoop dat het zo een beetje duideliljk is en dat iemand mij kan helpen. Alvast bedankt!

[Patrick_6369]

Noh, nou vind ik mezelf al een bovengemiddelde user in PowerQuery (vergeleken met mijn collega's die desondanks dezelfde training hebben gehad). Maar dit vind ik wel een flinke uitdaging. Ik zou ook niets weten wat om kan met een altijd wijzigende structuur.
Kun je aangeven in hoeverre deze structuur vast is (tot op welk niveau zijn de folders en sub-folders altijd gelijk) en wat is het max. aantal niveaus? Wellicht is een workaround door allemaal subqueries te maken van lagere mappen en die later samen te voegen een optie?

[F_J_K]

https://exceloffthegrid.com/power-query-file-attributes/ maar ik weet niet of je dat per subfolder kan automatiseren.

Zonder powerquery:
Zijn Doc 1 - 01 etc vaste namen? Dan heb je al een quick&dirty inhoudsopgave door semi-handmatig (eenmalig werk) =ALS.FOUT([Doc 1 - 01.xlsx]Blad1!$A$1;"") te doen o.i.d. Waar je dan de file locatie laat afhangen van de voorgaande regel.

Lege regel = geen file.

[Bobbje]

Patrick_6369 schreef op vrijdag 17 juli 2020 @ 12:04:
Noh, nou vind ik mezelf al een bovengemiddelde user in PowerQuery (vergeleken met mijn collega's die desondanks dezelfde training hebben gehad). Maar dit vind ik wel een flinke uitdaging. Ik zou ook niets weten wat om kan met een altijd wijzigende structuur.
Kun je aangeven in hoeverre deze structuur vast is (tot op welk niveau zijn de folders en sub-folders altijd gelijk) en wat is het max. aantal niveaus? Wellicht is een workaround door allemaal subqueries te maken van lagere mappen en die later samen te voegen een optie?

Ik ken PowerQuery nu letterlijk 3 uur dus ik ben ook niet bepaald een expert.

Het diepste wat er gaat is 6 niveuas maar de structuur is niet vast the noemen qua naamgeving. Dat maakt het ook moeilijk verwacht ik.

F_J_K schreef op vrijdag 17 juli 2020 @ 12:55:
https://exceloffthegrid.com/power-query-file-attributes/ maar ik weet niet of je dat per subfolder kan automatiseren.

Zonder powerquery:
Zijn Doc 1 - 01 etc vaste namen? Dan heb je al een quick&dirty inhoudsopgave door semi-handmatig (eenmalig werk) =ALS.FOUT([Doc 1 - 01.xlsx]Blad1!$A$1;"") te doen o.i.d. Waar je dan de file locatie laat afhangen van de voorgaande regel.

Lege regel = geen file.

Het zijn ook geen vaste namen dus daarmee kan ik ook niet spelen. Enigste wat ik kan bedenken is eventueaal spelen met de bestandlocatie die wordt meegegeven.

[I-King]

Is dat niet wat hier beschreven staat? https://trumpexcel.com/list-file-names-power-query/

Edit: Aah verrek, de nuance zit 'm in de submap weer te geven, óók als er dus lege mappen zijn.

Ik heb ooit in een grijs verleden een 'tabelconversie' in PowerQuery omgezet naar een Functie. Je kunt dan een kolom toevoegen bestaande uit deze functie.

Zoals ik het zie:
- De lijst weer samenvoegen tot unieke waardes per (sub)folder
- De functiekolom toevoegen (via merge) (zodat daar de inhoud van weergegeven wordt en de map uitgevouwen wordt)
- Dubbelen evt filteren.

Maar ik ken er dus niet genoeg meer van hoe het precies heet en/of moet (klok/klepel).

[ Voor 90% gewijzigd door I-King op 17-07-2020 14:56 ]

[Bobbje]

I-King schreef op vrijdag 17 juli 2020 @ 14:49:
Is dat niet wat hier beschreven staat? https://trumpexcel.com/list-file-names-power-query/

Edit: Aah verrek, de nuance zit 'm in de submap weer te geven, óók als er dus lege mappen zijn.

Nee net niet. Want hier komen geen folder in de lisjt te staan maar alleen de de bestanden. Er zitten ook lege folder welke moeten worden meegenomen, die niet herleid kunnen worden op deze manier omdat er geen bestand in zit.

Edit: de edit klopt

[MrMonkE]

Je gaat een inhoudsopgave making aan de hand van de data zodat de klant met die inhoudsopgave kan zien of een folder express leeg is. Klinkt onzuiver.

Kun je niet gewoon wegkomen met een 'legefolder.text' in lege mappen te plaatsen?
Ik weet dat het een beetje ranzig is.
En dan deze later weer te verwijderen.

[Bobbje]

MrMonkE schreef op vrijdag 17 juli 2020 @ 14:55:
Je gaat een inhoudsopgave making aan de hand van de data zodat de klant met die inhoudsopgave kan zien of een folder express leeg is. Klinkt onzuiver.

Kun je niet gewoon wegkomen met een 'legefolder.text' in lege mappen te plaatsen?
Ik weet dat het een beetje ranzig is.
En dan deze later weer te verwijderen.

De lege folder.txt bestanden zat kk ook aan te denken, dan kan ik het wel op basis van de bestanden opbouwen. Zeker in combinatie met Excel formules erbij.

[gwystyl]

Misschien niet wat je zoekt, maar kan je niet met het oude commando "dir", en de uitkomst daarvan in een .txt stoppen? Volgens mij kan dat door er > <naam>.txt achter te zetten. Die .txt kan je dan weer inlezen in Excel als je dat graag zou willen. Filmpje waarin dat in Indiaas Engels wordt uitgelegd...

Verder heb ik een google search gedaan die je waarschijnlijk zelf ook al geprobeerd hebt, maar misschien zit hier nog wat tussen? Ik weet alleen niet of daar bijvoorbeeld ook lege mappen/bestanden mee worden genomen.

[ Voor 15% gewijzigd door gwystyl op 18-07-2020 20:29 ]