POWER QUERY fichier Excel

Bonjour,

J'ai trois fichier de base, et j'aimerai que pour les deux premiers fichiers on puisse éclater pour que chaque matricule ai une ligne par jour de dates avec en bout de lignes une concaténation matricule/motif/date.

Voici les deux codes M de mes deux fichiers.

Fichier 1 :

let
// 1. Charger le dossier
Source = Folder.Files(...),

// 2. Garder uniquement les fichiers Excel
FichiersExcel = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx") or Text.EndsWith([Name], ".xlsm")),

// 3. Charger le contenu de chaque fichier
AjoutContenu = Table.AddColumn(FichiersExcel, "Contenu", each Excel.Workbook([Content], null, true)),

// 4. Développer les feuilles
Feuilles = Table.ExpandTableColumn(
AjoutContenu,
"Contenu",
{"Name", "Data", "Kind"},
{"Feuille", "Data", "Kind"}
),

// 5. Garder uniquement les onglets
Onglets = Table.SelectRows(Feuilles, each [Kind] = "Sheet"),

// 6. Ajouter le nom du fichier source
AjoutNomFichier = Table.AddColumn(Onglets, "Fichier", each [Name]),

// 7. Développer les données des feuilles
Combine = Table.ExpandTableColumn(
AjoutNomFichier,
"Data",
Table.ColumnNames(AjoutNomFichier[Data]{0})
),
#"Colonnes supprimées" = Table.RemoveColumns(Combine,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Attributes", "Folder Path", "Kind", "Fichier", "Date created"}),
#"Premières lignes supprimées" = Table.Skip(#"Colonnes supprimées",2),
#"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées", [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{ {"SYNLAB_Gestion", type text}, {"Matricule", Int64.Type}, {"Nom et prénom", type text}, {"Bulletin", type text}, {"Code", type text}, {"Libellé", type text}, {"Début", type date}, {"Fin", type date}, {"Nb. jrs. calendaires", type number}, {"Nb. jrs. ouvrés", type number}, {"Nb. jrs. ouvrables", type number}, {"Nb. jrs. travaillés", type number}}),
#"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([Nom et prénom] <> null and [Nom et prénom] <> "") and ([Bulletin] <> "") and ([Code] <> "Code")),
#"Type modifié1" = Table.TransformColumnTypes(#"Lignes filtrées",{{"Matricule", type text}}),
#"Colonnes renommées" = Table.RenameColumns(#"Type modifié1",{{"SYNLAB_Gestion", "Société"}}),
#"Texte inséré entre les délimiteurs" = Table.AddColumn(#"Colonnes renommées", "Texte entre les délimiteurs", each Text.BetweenDelimiters([Bulletin], "_", "_", 2, 0), type text),
#"Colonnes renommées1" = Table.RenameColumns(#"Texte inséré entre les délimiteurs",{{"Texte entre les délimiteurs", "Date bulletin"}}),
#"Colonne personnalisée ajoutée" = Table.AddColumn(#"Colonnes renommées1", "Personnalisé", each let splitDébut = Splitter.SplitTextByDelimiter("/", QuoteStyle.None)(Text.From([Début], "fr-FR")), splitFin = Splitter.SplitTextByDelimiter("/", QuoteStyle.None)(Text.From([Fin], "fr-FR")) in Text.Combine({[Matricule], "_", [Code], "_", Text.Combine(splitDébut, "."), "_", Text.Combine(splitFin, ".")}), type text),
#"Colonnes renommées2" = Table.RenameColumns(#"Colonne personnalisée ajoutée",{{"Personnalisé", "Clé"}, {"Code", "PAIE_Code"}, {"Début", "PAIE_Début"}, {"Fin", "PAIE_Fin"}}),
#"Lignes filtrées1" = Table.SelectRows(#"Colonnes renommées2", each true)
in
#"Lignes filtrées1"

Fichier 2 :

let
Source = Folder.Files(...),
#"Fichiers masqués filtrés1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier (2)", each #"Transformer le fichier (2)"([Content])),
#"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
#"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier (2)"}),
#"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier (2)", Table.ColumnNames(#"Transformer le fichier (2)"(#"Exemple de fichier (2)"))),
#"Colonnes supprimées" = Table.RemoveColumns(#"Colonne de tables développée1",{"Source.Name", "Column1"}),
#"Premières lignes supprimées" = Table.Skip(#"Colonnes supprimées",2),
#"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées", [PromoteAllScalars=true]),
#"Type modifié1" = Table.TransformColumnTypes(#"En-têtes promus",{{"MMAT", type text}, {"MCONTRAT", type text}, {"MMOTIF", type text}, {"TYPE1", type text}, {"TYPE", type text}, {"DDEB", type text}, {"DFIN", type text}, {"NBH", type text}, {"HDEB", type text}, {"HFIN", type text}}),
#"Autres colonnes supprimées" = Table.SelectColumns(#"Type modifié1",{"MMAT", "MMOTIF", "TYPE", "DDEB", "DFIN", "NBH"}),
#"Lignes filtrées" = Table.SelectRows(#"Autres colonnes supprimées", each ([TYPE] <> "" and [TYPE] <> "TYPE")),
#"Lignes triées" = Table.Sort(#"Lignes filtrées",{{"MMAT", Order.Ascending}}),
#"Colonne personnalisée ajoutée" = Table.AddColumn(#"Lignes triées", "Personnalisé", each Text.Combine({
[MMAT],
"_",
[MMOTIF],
"_",
Date.ToText(Date.From([DDEB]), "dd"),
Text.PadStart(Date.ToText(Date.From([DDEB]), "MM"), 3, "."),".",
Date.ToText(Date.From([DDEB]), "yyyy"),"_",
Date.ToText(Date.From([DFIN]), "dd"),
Text.PadStart(Date.ToText(Date.From([DFIN]), "MM"), 3, "."),
".",
Date.ToText(Date.From([DFIN]), "yyyy")
})),
#"Doublons supprimés" = Table.Distinct(#"Colonne personnalisée ajoutée"),
#"Lignes filtrées1" = Table.SelectRows(#"Doublons supprimés", each not Text.Contains([MMAT], "T") and not Text.Contains([MMAT], "M") and not Text.Contains([MMAT], "B"))
in
#"Lignes filtrées1"

Est-il possible d'avoir le code M final pour chaque fichier ?

Bonjour Valentine,

Comme il s'agit de langage = code = utilisation des balises faites pour ça

La prochaine fois, merci de mettre ton "code" entre balises STP grâce au bouton

image

Ceci dit, sans les fichiers anonymisés, ça me sembler compliqué de te répondre, je vais quand même regarder

Edit, essaye ceci (mais sans fichier pas certain du résultat)

let
// 1. Charger le dossier
Source = Folder.Files(...),

// 2. Garder uniquement les fichiers Excel
FichiersExcel = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx") or Text.EndsWith([Name], ".xlsm")),

// 3. Charger le contenu de chaque fichier
AjoutContenu = Table.AddColumn(FichiersExcel, "Contenu", each Excel.Workbook([Content], null, true)),

// 4. Développer les feuilles
Feuilles = Table.ExpandTableColumn(
AjoutContenu,
"Contenu",
{"Name", "Data", "Kind"},
{"Feuille", "Data", "Kind"}
),

// 5. Garder uniquement les onglets
Onglets = Table.SelectRows(Feuilles, each [Kind] = "Sheet"),

// 6. Ajouter le nom du fichier source
AjoutNomFichier = Table.AddColumn(Onglets, "Fichier", each [Name]),

// 7. Développer les données des feuilles
Combine = Table.ExpandTableColumn(
AjoutNomFichier,
"Data",
Table.ColumnNames(AjoutNomFichier[Data]{0})
),
#"Colonnes supprimées" = Table.RemoveColumns(Combine,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Attributes", "Folder Path", "Kind", "Fichier", "Date created"}),
#"Premières lignes supprimées" = Table.Skip(#"Colonnes supprimées",2),
#"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées", [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{ {"SYNLAB_Gestion", type text}, {"Matricule", Int64.Type}, {"Nom et prénom", type text}, {"Bulletin", type text}, {"Code", type text}, {"Libellé", type text}, {"Début", type date}, {"Fin", type date}, {"Nb. jrs. calendaires", type number}, {"Nb. jrs. ouvrés", type number}, {"Nb. jrs. ouvrables", type number}, {"Nb. jrs. travaillés", type number}}),
#"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([Nom et prénom] <> null and [Nom et prénom] <> "") and ([Bulletin] <> "") and ([Code] <> "Code")),
#"Type modifié1" = Table.TransformColumnTypes(#"Lignes filtrées",{{"Matricule", type text}}),
#"Colonnes renommées" = Table.RenameColumns(#"Type modifié1",{{"SYNLAB_Gestion", "Société"}}),
#"Texte inséré entre les délimiteurs" = Table.AddColumn(#"Colonnes renommées", "Texte entre les délimiteurs", each Text.BetweenDelimiters([Bulletin], "_", "_", 2, 0), type text),
#"Colonnes renommées1" = Table.RenameColumns(#"Texte inséré entre les délimiteurs",{{"Texte entre les délimiteurs", "Date bulletin"}}),
#"Colonne personnalisée ajoutée" = Table.AddColumn(#"Colonnes renommées1", "Personnalisé", each let splitDébut = Splitter.SplitTextByDelimiter("/", QuoteStyle.None)(Text.From([Début], "fr-FR")), splitFin = Splitter.SplitTextByDelimiter("/", QuoteStyle.None)(Text.From([Fin], "fr-FR")) in Text.Combine({[Matricule], "_", [Code], "_", Text.Combine(splitDébut, "."), "_", Text.Combine(splitFin, ".")}), type text),
#"Colonnes renommées2" = Table.RenameColumns(#"Colonne personnalisée ajoutée",{{"Personnalisé", "Clé"}, {"Code", "PAIE_Code"}, {"Début", "PAIE_Début"}, {"Fin", "PAIE_Fin"}}),
#"Lignes filtrées1" = Table.SelectRows(#"Colonnes renommées2", each true)
in
// Une ligne par jour entre PAIE_Début et PAIE_Fin inclus
#"Dates par jour ajoutées" = Table.AddColumn(
    #"Lignes filtrées1",
    "Date",
    each
        if [PAIE_Début] = null or [PAIE_Fin] = null or [PAIE_Fin] < [PAIE_Début]
        then {}
        else
            List.Dates(
                [PAIE_Début],
                Duration.Days([PAIE_Fin] - [PAIE_Début]) + 1,
                #duration(1, 0, 0, 0)
            ),
    type list
),

// Développer la liste en une ligne par date
#"Dates développées" = Table.ExpandListColumn(
    #"Dates par jour ajoutées",
    "Date"
),

#"Type Date appliqué" = Table.TransformColumnTypes(
    #"Dates développées",
    {{"Date", type date}}
),

// Concaténer matricule, motif et date au format jour.mois.année
#"Clé journalière ajoutée" = Table.AddColumn(
    #"Type Date appliqué",
    "Clé journalière",
    each
        Text.Combine(
            {
                Text.From([Matricule]),
                Text.From([PAIE_Code]),
                Date.ToText([Date], "dd.MM.yyyy", "fr-FR")
            },
            "_"
        ),
    type text
)
in
#"Clé journalière ajoutée"

A+

Rechercher des sujets similaires à "power query fichier"