Power Query grouper plusieurs feuilles qui n'ont pas le même format avec normalisation des noms des entêtes

[elbahja79]

bonjour

je souhaite combiner plusieurs classeurs/feuilles en un seul tableau.

les données contenues dans chaque feuille contiennent :
1- des inforamtions pour connaitre les informations relatives à la feuille ( éditeur/ date édition/validé/....)
2- Un tableau: les données des tableaux sont compris entre deux colonnes fixes nommées pour l'exemple joint "col fixe1" et "col fixe2" mais le nombre entre ces deux colonnes varient d'une feuille à une autre (2, 3 , 4 .... colonnes)

je veux dans un premier temps avoir un tableau avec le format suivant (col fixe1,Col fixe 2, Attribut, Valeur) avec attribut comporte les noms des colonnes.

je me demande aussi est ce possible de normaliser les noms des colonnes(attribut) (par exemple ville est écrit par chacun de sa façon villes, city, cities, Ville....et je veux qu'elle soit normalisée)

Merci

 

[mromain]

Bonjour elbahja79,

Pour la première partie de ton problème, à savoir agréger les données dans un unique tableau à 4 colonnes (Col fixe1,Col fixe 2, Attribut et Valeur), tu peux essayer avec la requête ci-dessous.

Elle est à utiliser depuis un autre classeur. Il faut également adapter l'étape excelFilePath pour indiquer l'emplacement du fichier de données (le fichier combiner plusieurs feuilles.xlsx que tu nous as fourni).

let
    
    // intitulé des 2 colonnes fixes
    col1Name = "Col Fixe1",
    col2Name = "Col Fixe2",
    
    // fonction permettant de récupérer les données "propres" à partir des données brutes d'une feuille
    getSheetData = (col1Name as text, col2Name as text, sheetData as table) as nullable table =>
        let
            // ajouter une colonne permettant d'identifier la ligne des header (celle contenant les 2 intitulés de colonnes fixes)
            AddColumn_HeaderFlag = Table.AddColumn(sheetData, "FlagHeaderRow", each let rowValues = Record.ToList(_) in List.Contains(rowValues, col1Name) and List.Contains(rowValues, col2Name), type logical),
            // supprimer les premières lignes
            DeleteFirstRows = Table.Skip(sheetData,List.PositionOf(AddColumn_HeaderFlag[FlagHeaderRow], true)),
            // promouvoir les entêtes
            PromoteHeaders = Table.PromoteHeaders(DeleteFirstRows, [PromoteAllScalars=true]),
            // dépivoter les autres colonnes
            Unpivot = Table.UnpivotOtherColumns(PromoteHeaders, {col1Name, col2Name}, "Attribut", "Valeur")
        in
            try Unpivot otherwise null,
    
    excelFilePath = "C:\Folder\combiner plusieurs feuilles.xlsx",
    fileContent = Excel.Workbook(File.Contents(excelFilePath), null, true),
    AddColumn_CleanData = Table.AddColumn(fileContent, "CleanData", each getSheetData(col1Name, col2Name, [Data]), type nullable table),
    Filter_WithCleanData = Table.SelectRows(AddColumn_CleanData, each [CleanData] <> null),
    SelectColumns = Table.SelectColumns(Filter_WithCleanData,{"Name", "CleanData"}),
    Expand_CleanData = Table.ExpandTableColumn(SelectColumns, "CleanData", {col1Name, col2Name, "Attribut", "Valeur"}, {col1Name, col2Name, "Attribut", "Valeur"})
in
    Expand_CleanData

A+

 

[Hasco]

Bonjour
,
Voici la requête de @mromain quelque peu modifiée et complétée de quelques lignes pour le remplacement des mots de la colonne attribut.

Dans la feuille Objectif vous verrez le résultat.
Si les données source des feuilles à compiler changent, enregistrez le classeur avant de rafraîchir la requête.
Vérifiez que le chemin vers le classeur est le bon dans la cellule A1 de objectif, faites recalculer la cellule au besoin ou modifiez manuellement.

Le remplacement est fait ligne à ligne. On peut le faire aussi par fusion avec la table de correspondance, mais cela demande plus de lignes. Voir requête "Données (2)"

@mromain, ne m'en veuillez pas d'avoir utiliser votre requête de base, la flemme de tout refaire.
J'ai modifié la façon de trouver la ligne d'entête des feuilles, le nom "Col Fixe1" étant toujours en colonne A. Si la réalité dit autre chose alors votre méthode sera à appliquer.

Cordialement

 

[elbahja79]

bonsoir
Merci @mromain et @Hasco pour vos réponses, ils m'ont éclaircit la voie.

pour ses propositions comment je peux la modifier pour l'appliquer à plusieurs classeurs sur un dossier et ses dossiers (choisir les classeurs dont le nom contient un mot clé "combiner" par exemple et seulement les feuilles dont le nom contient un terme "feuil" par exemple)

pour la proposition de @Hasco, est ce qu'il y a possibilité d'introduire dans la table de remplacements des nouvelles nom de colonnes qui s'ajoutent car la liste que j'ai n'est pas exhaustive et s'ajoute d'une façon permanente.

Merci infiniment de nouveau pour vos propositions.

 

[Hasco]

Bonjour,

pour ses propositions comment je peux la modifier pour l'appliquer à plusieurs classeurs sur un dossier..

1 - ce n'était pas dans la demande initiale
2 - votre demande et votre titre de discussion laissaient supposer que vous aviez un minimum de connaissances dans power query.
3 - qu'est-ce que vous avez essayé pour répondre à votre demande ? Par exemple
- Données/Obtenir des données/A partir d'un fichier/ A partir d'un dossier
- Quand vous êtes à l'étape de l'assistant qui vous présente les fichiers du dossier, cliquez sur le bouton "Transformer les données"
- trouver un tuto sur internet pour compiler (consolider) les fichiers d'un dossier
4 - si vous avez des difficultés, et comme la question est différente de votre demande initiale, creez une nouvelle discussion en y joignant un exemple de ce que vous avez déjà fait. Ne vous contentez pas de 'demander' participez un minimum :

est ce qu'il y a possibilité d'introduire dans la table de remplacements des nouvelles nom de colonnes...

Bien-sûr : mettez dans la colonne gauche ("Items") les mots à remplacer dans chacune de leur version (singulier, pluriel, masculin, féminin) s'il peut en exister plusieurs version, puis dans la colonne de droite "Remplacements" les mots de remplacement.

P.S. dans vos discussions évitez d'écrire des phrases tout en gras, comme le tout majuscule, c'est pénible à lire

 

[mromain]

Bonjour à tous,

@Hasco

ne m'en veuillez pas d'avoir utiliser votre requête de base, la flemme de tout refaire

Pas de soucis, bien évidemment

@elbahja79
Je suis plutôt d'accord avec Hasco.
Pour cette partie-là :

l'appliquer à plusieurs classeurs sur un dossier et ses dossiers (choisir les classeurs dont le nom contient un mot clé "combiner" par exemple et seulement les feuilles dont le nom contient un terme "feuil" par exemple)

Il faut regarder du côté de Données / Obtenir des données / A partir d'un fichier / A partir d'un dossier et tu devrais t'en sortir.

N'hésite pas à revenir vers nous si tu as un soucis, et comme le dit Hasco, à créer une nouvelle discussion si besoin et fournir un exemple que tu as déjà fait.

A+