Power Query Entetes parfois sur une ligne parfois sur deux

[lbahjaoui]

Bonjour

j'ai un ensemble de classeurs que j'essaye de combiner les feuilles qu'elles contiennent qui commencent par U;
le resultat doit être dans un classeur à part.

je veux renommer la premiere colonne par "Utilisateur" et la derniere colonne par "EstVerifie"
Ajouter une colonne avec le nom de la feuille.


puis je veux garder ces trois colonnes et depivoter les autres colonnes mais le nom des autres colonnes représente quelques difficultés:
1- parfois en majuscule et parfois en miniscule
2- parfois contient les accents parfois non (par exemple école, ecole, écôle,)
3-le problème majeur est que certains nom de colonnes sont ecrits dans une feuille sur une ligne et parfois sur deux lignes.

une petite illustration est sur les deux classeurs joints.

Merci

 

[mromain]

Bonjour lbahjaoui, le forum,

Tu trouveras ci-joint un essai.
La requête :

• parcourt les fichiers d'un dossier
• filtre sur les fichiers .xlsx
• récupère le contenu des fichiers et filtre sur les feuilles dont le nom commence par U
• transforme les données des feuilles en :
  • recréant les entêtes (à partir de la ou des lignes initiales)
  • renommant la première et dernière colonne
  • supprimant les colonnes sans données
  • dépivotant les autres colonnes

let
    folderPath = "C:\LeDossier",
    folderFiles = Folder.Files(folderPath),
    filterXlsx = Table.SelectRows(folderFiles, each ([Extension] = ".xlsx")),
    selectFilesColumns = Table.SelectColumns(filterXlsx,{"Name", "Content"}),
    binariesToExcel = Table.TransformColumns(selectFilesColumns,{{"Content", Excel.Workbook, type table}}),
    expandExcelData = Table.ExpandTableColumn(binariesToExcel, "Content", {"Name", "Data", "Item", "Kind", "Hidden"}, {"SheetName", "Data", "Item", "Kind", "Hidden"}),
    filterSheets = Table.SelectRows(expandExcelData, each Text.StartsWith([SheetName], "U") and [Kind] = "Sheet"),
    fnTransformDataSheet = (rawDataSheet as table) as table =>
        let
            rawColsName = Table.ColumnNames(rawDataSheet),
            newColsName =
                let
                    rawHeaderRows = Table.SelectRows(rawDataSheet, each ([Column1] = null)),
                    mergeRows = List.Transform(rawColsName, each let newHeader = Text.Combine(List.RemoveNulls(List.Transform(Table.Column(rawHeaderRows, _), Text.Trim)), " ") in if newHeader = "" then _ else newHeader),
                    changeFirstAndLast = {"Utilisateur"} & List.RemoveFirstN(List.RemoveLastN(mergeRows, 1), 1) & {"EstVerifie"}
                in
                    changeFirstAndLast,
         
            dataRows = Table.SelectRows(rawDataSheet, each ([Column1] <> null)),
            renameColumns = Table.RenameColumns(dataRows,List.Zip({rawColsName, newColsName})),
            colsWithData = List.RemoveNulls(List.Transform(newColsName, each if List.Count(List.RemoveNulls(Table.Column(renameColumns, _))) = 0 then null else _)),
            removeEmptyColumns = Table.SelectColumns(renameColumns, colsWithData),
            unpivotColumns = Table.UnpivotOtherColumns(removeEmptyColumns, {"Utilisateur", "EstVerifie"}, "Attribut", "Valeur")
        in
            unpivotColumns,
    transformSheets = Table.TransformColumns(filterSheets, {"Data", fnTransformDataSheet, type table}),
    selectSheetsColumns = Table.SelectColumns(transformSheets,{"Name", "SheetName", "Data"}),
    expandSheetsData = Table.ExpandTableColumn(selectSheetsColumns, "Data", {"Utilisateur", "EstVerifie", "Attribut", "Valeur"}, {"Utilisateur", "EstVerifie", "Attribut", "Valeur"})
in
    expandSheetsData

Par contre, cette requête ne fait pas la traduction des colonnes. A ma connaissance, aucune fonction Power Query ne permettra de faire la correspondance entre Café litéraire et Café littéraire par exemple.

Il te faudrait rajouter une table de correspondance pour faire cette traduction.

Ci-joint un fichier exemple qui fonctionne avec les deux fichiers que tu as fournis.
Pour faire fonctionner la requête, il faut adapter le dossier analysé dans la première étape (folderPath = "C:\LeDossier").
Tu pourras remarquer que certains attributs ont pour intitulé ColumnX. Cela vient du fait que certaines colonnes n'ont pas d'intitulé dans les fichiers fournis (par exemple, les colonnes D et F de l'onglet U003 du fichier 010120222.xlsx).

A+

 

[lbahjaoui]

merci @mromain pour la solution et pour les remarques