Power Query Transformer les données de plusieurs feuilles et plusieurs fichiers

[TechnoDust]

Bonjour à tous,

Là où je bosse les statistiques de l'année sont présentées dans un fichier excel de 80 feuilles. Toutes les stats y sont présentés en tableau croisé complexes (en-tête sur deux lignes, des fusions dans tout les sens, aucun tableau n'est le même entre feuille...) et j'ai une vingtaine de fichier (1 par an). mon objectif est de constituer une base avec l'ensemble des données aplanies pour faire de l'analyse.
Je souhaite extraire et aplanir les données de 21 feuilles par fichier et sur mes 10 fichiers. J'ai fait une requête "test" qui semble fonctionner sur l'ensemble des tableaux avec Power Query.
Ma question est comment faire pour appliquer cette requête sur les 21 feuilles des 10 fichiers (soit 210 feuilles). Je pourrais manuellement changer ma source et charger la requête mais avec les 210 feuilles, bon courage...

Je suis auto-formé sur power query, et depuis peu je creuse Power BI mais ne trouve nulle part réponse à ma question.
Merci beaucoup pour votre aide.

 

[chris]

Bonjour

PowerQuery peut traiter tous les classeurs d'un dossier si la structure des onglets est la même

Sans plus d'info sur le contenu /structure des onglets difficile de t'aider davantage

 

[TechnoDust]

Bonjour Chris,

Comme précisé les tableaux varient entre feuilles. Tu as en tête de transformer la requête en fonction ?

En PJ le fichier pour l'année 2016, j'ai retiré les onglets qui ne m’intéresse pas pour alléger le fichier.

Encore une fois je pense avoir trouver une requête assez générale qui me permet d'aplanir et transformer suffisamment les tableaux. Elle est également dans le fichier.

Merci pour ton regard,

 

[merinos]

Salut @TechnoDust ,

J'ai créé un query qui assemble les fichiers du type "BSA_*.* qui sont dans MON directory download....

Je lis juste les feuilles "P4"...

Merinos

 

[Hasco]

Bonjour le fil,

Une requête traitant toutes les feuilles d'un classeur et dont le nom commence par "P".
Comme vos tableaux retournent jusqu'à 69 colonnes dont beaucoup d'inutiles, vous verrez plusieurs étapes pour ne sélectionner par une petite fonction que les colonnes 1 à colonne Total +3.


Pour le traitement d'une table j'ai simplement un peu modifier votre requête en supprimant quelques étapes inutiles et celles qui faisaient appel aux listes de colonnes. Voir requête "Exemple Traitement Feuille"

Pour le nommage des colonnes "Produit" et "Donnée" je n'utilise pas le nom d'origine qui peut varier d'une feuille à une autre, mais :

= Table.ColumnNames(#"En-têtes promus"){0}

et

Table.ColumnNames(#"En-têtes promus"){1}

Qui retournent les noms des deux premières colonnes de l'étape précédente.

N'oubliez pas de changer le répertoire source des données.

Pour faire tout un dossier de plusieurs fichiers nous verrons après.

Cordialement

P.S. Merci pour la nostalgie de mon passage dans ces îles.

Petites questions diverses :
1 - Pourquoi conserver les lignes Total, qui pourraient être déduites ou calculées par TCD ou autres ?
2 - Pourquoi utiliser des fichiers .xls en 2019 ?

 

[chris]

Bonjour

J'ai traité un peu différemment en éliminant les totaux, récupérant la Zone géographique comme ILES DU VENT, et la Catégorie comme FRUITS

Cependant l'approche de Roblochon me paraissant plus efficace sur d'autres aspects je vais combiner les 2 et reposté.

Un second classeur serait bienvenu pour tester...

 

[chris]

RE

Bon je n'avais pas vu l'étendue des dégâts : avec jusquà 4 tableaux superposés dont ceux du bas de structure différente de ceux du haut on ne peut obtenir quelque chose de cohérent....

 

[Hasco]

Bonjour

@chris , je n'avais pas vu moi non plus
Peut-être qu'en ayant accès directement à la source : Direction de l'Agriculture pourrions nous en sortir quelque chose de plus adapté ?

Cordialement

 

[TechnoDust]

Bonjour à tous !
Merci beaucoup pour vos nombreux retours, j'ai essayé de rentrer dans chacun de vos fichiers pour comprendre les opérations réalisées mais garde cependant quelques questions pour bien comprendre.
Je découvre encore le langage M donc pardonnez moi si je pose une question bête.
@merinos
Le paramètre à entrer pour ta fonction P4 est donc l'emplacement du fichier (C://user....) ? Si je comprends bien cette fonction ne permet donc que de lire les onglets P4 des différents fichier que j'entre en paramètre ?

@Roblochon le résultat de ta fonction fonctionne très bien à mon sens. Ce fichier excel était habituellement enregistré en PDF et faisait office de publication. La ligne "source : direction de l'agriculture" est donc une information pour les lecteurs...
J'avais intentionnellement retiré certaines parties des onglets pour secret statistique mais vous avez les onglets plus "complet" dans le wetransfer plus bas.
Les données qui m’intéresse sont celles des listes produits, je pense pouvoir me débarrasser des autres parties des tableaux grâce aux filtres sur erreurs et valeurs nulles. De retour au bureau demain je m'y attèle.

Puis-je abuser de ton temps et avoir des indices sur comment traiter ensuite l'ensemble des fichiers d'un dossier ? S'il s'agit de langage M comme pour le traitement des onglets, je vais me plonger dans l'autoformation et essayer de trouver la solution

Je vous ai remis les fichiers de 2016 et 2019 complet dans ce wetransfer, vous remarquerez que les noms des onglets ont changés au cours des années. Je pense qu'il n'est pas difficile de modifier le noms des onglets pour qu'ils commencent par "P..". Les n° qui m'intéresse sont restés les mêmes.

Ravi d'avoir raviver des souvenirs ! Si tu es déjà passé par ici et eu l'occasion de voir certains aspects de la vie professionnelle, tu dois donc te douter de pourquoi nous avons encore des fichiers en .xls. Nous avons un peu de retard sur beaucoup de choses et dans l'administration agricole les systèmes d'informations sont très en retard...

 

[TechnoDust]

.S. Merci pour la nostalgie de mon passage dans ces îles.

Petites questions diverses :
1 - Pourquoi conserver les lignes Total, qui pourraient être déduites ou calculées par TCD ou autres ?
2 - Pourquoi utiliser des fichiers .xls en 2019 ?

Je n'ai pas l'intention de conserver les lignes TOTAL., je vais ajouter dans la requête l'ajout d'une colonne valeur conditionnelle permettant de les rendre nulles et les sortir ensuite.

Je n'arrive pas à relancer ta requête ToutesLesFeuillesPN car il semble y avoir un soucis avec le champ [Kind]. Que dois-je modifier pour pouvoir la lancer par rapport à ce champ ?
Merci beaucoup @Roblochon

 

[Hasco]

Re,

Plutôt qu'un webtransfert, allégez et anonymisez vos fichiers pour les joindre ici.
Pour les faire maigrir un peu plus encore, enregistrez les en .xlsx (fichier sans macro).
Ils seront toujours utiles aux futurs demandeurs qui auront le même problème que vous.

De plus, peu de répondeurs (dont bibi) vont allez chercher des fichiers ailleurs qu'ici.

Que dois-je modifier pour pouvoir la lancer par rapport à ce champ ?

Rien ou supprimer ce critère de sélection.
J'ai mis ce critère car je ne savais pas s'il pouvait y avoir dans vos fichiers d'autres noms commençant par "P" qui ne soient pas de type Feuille (Kind="Sheet")

A partir d'une photo nous ne pouvons pas savoir d'où provient votre erreur, (étape précédente ou ailleurs), ce que vous avez fait avant etc...

La sélection des colonnes de chaque feuille se fait sur la position de la colonne "TOTAL" -1 avant transposition ce qui permet d'avoir moins de lignes à supprimer ensuite

each List.PositionOf( Record.ToTable([Data]{1})[Value],"TOTAL")-1

Pour les "TOTAL" en première colonnes, la fonction de traitement d'une feuille se fait par sélection des lignes qui ne contiennent pas "total" en première colonne (après mise en minuscule)

Table.SelectRows(#"Autres colonnes supprimées1", each not Text.Contains(Text.Lower([Produit]), "toal"))

Tout ceci pour le fichier BSA_2016.xlsm
Dans BSA.zip vous avez 3 fichiers
BSA_2016 - 1.xlsm ' fichier allégé de données
BSA_2016 - 2.xslm ' fichier allégé de données

PQ-BSA_2016.xlsm 'fichier contenant la requête qui interroge les fichiers du dossier dont les noms commencent par "BSA_"

Avant d'actualiser la requête, changer le répertoire source à l'étape 'Source' de "RQ-ClasseursDuDossier"

Cordialement