PowerQuery :: SPUC2R :: Dépivoter le contenu des cellules comportant des séparateurs à deux niveaux

[oguruma]

Supposons que nous ayons ce type de fichier - exemple de commerciaux qui ont une dotation de matériel et des conditions de frais
(les données n'ont aucun sens c'est juste pour la démonstration).

Il n'est pas impossible que vous rencontriez ce type de données comme celle-ci

[ce qui m'est arrivé pour suivre des traitements batch - une log de traitement qu'il faut découper)

le but est de transformer ce tableau pour obtenir ceci

Pour cela :
- les formules à oublier !
- en VBA Oui c'est possible mais pour les chevronnés.... et un peu "casse cou"
- en PowerQuery ==> Bien plus simple.

L'idée est donc de produire une fonction capable d'accepter : la table de données à traiter (soit un TS au format texte soit une table déjà intégrée dans powerquery), le champ cellule à dépivoter, le séparateur de 1er niveau, le séparateur de 2ème niveau, et les noms des champs résultats. Le résultat renvoyé est une table. Voir le fichier joint.

La fonction : fnSplitUnpivotCells
let fnSplitCell = (
pTbl as any,
pDelim1 as text,
pDelim2 as text,
pSplitField as text,
pFieldName as text,
pFieldValue as text
) as table =>

let
// Chargement des paramètres
Tbl=pTbl,
Delim_1=Text.Trim(pDelim1), ---> on prend les mesures nécessaires pour virer les espaces placés par erreur dans les paramètres
Delim_2=Text.Trim(pDelim2),
SplitField=Text.Trim(pSplitField),
SplitField_1=pSplitField & ".1",
SplitField_2=pSplitField & ".2",
NameField_1=pFieldName,
NameField_2=pFieldValue,

// Capture de la source de données
// On accepte soit le nom du tableau structuré soit le nom de la table en PowerQuery ---> c'est ici que l'on distingue le type de table (texte ou Pq)
Source = if pTbl is text then Excel.CurrentWorkbook(){[Name=Tbl]}[Content] else pTbl,

// Un peu de nettoyage pour s'assurer d'un bon cadrage
SupprSpace_1 = Table.TransformColumns(Source,{{SplitField, Text.Trim, type text}}),
Clean_1 = Table.TransformColumns(SupprSpace_1,{{SplitField, Text.Clean, type text}}),

// On force le type texte pour la cellule à unpivoter
ModifType_1 = Table.TransformColumnTypes(Clean_1,{{SplitField, type text}}),

// 1ère phase de découpage - préparation attribut valeur
Split_1 = Table.ExpandListColumn(Table.TransformColumns(ModifType_1, {{SplitField, Splitter.SplitTextByDelimiter(Delim_1, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), SplitField),
ModifType_2 = Table.TransformColumnTypes(Split_1,{{SplitField, type text}}),
SupprSpace_2 = Table.TransformColumns(ModifType_2,{{SplitField, Text.Trim, type text}}),

//2nde phase découpage pour avoir attribut et valeur
Split_2 = Table.SplitColumn(SupprSpace_2, SplitField, Splitter.SplitTextByDelimiter(Delim_2, QuoteStyle.Csv), {SplitField_1, SplitField_2}),

// On force le type text à nouveau sur le résultat
ModifType_3 = Table.TransformColumnTypes(Split_2,{{SplitField_1, type text}, {SplitField_2, type text}}),

// On donne un sens au nom des colonnes
RenColumns = Table.RenameColumns(ModifType_3,{{SplitField_1, NameField_1}, {SplitField_2, NameField_2}}),

// Nettoyage pour un bon cadrage
SupprSpace_3 = Table.TransformColumns(RenColumns,{{NameField_1, Text.Trim, type text}}),
SupprSpace_4 = Table.TransformColumns(SupprSpace_3,{{NameField_2, Text.Trim, type text}})

// Fin des traitements
in
SupprSpace_4
in
fnSplitCell

Exemple d'utilisation :

let
Source = fnSplitUnpivotCells
(
"TB_AGENTS",
";",
":",
"Dotation",
"Equipement accordé",
"Désignation")
in
Source

 

[Cousinhub]

Bonjour,
J'ai juste une question....
Pourquoi tant de manipulations pour un résultat obtenu en 4 lignes? (et quasiment tout piloté à la souris... sauf le nommage des 2 dernières colonnes, manuellement inséré dans la barre de formule et le renommage des étapes)

let
    
    Source = Excel.CurrentWorkbook(){[Name="TB_AGENTS"]}[Content],
    FractV = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Dotation", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Dotation"),
    FractH = Table.SplitColumn(FractV, "Dotation", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Equipement accordé", "Désignation"}),
    Trim = Table.TransformColumns(FractH,{{"Equipement accordé", Text.Trim, type text}, {"Désignation", Text.Trim, type text}})
    
in
    Trim

Bonne journée

 

[oguruma]

Bonjour,
J'ai juste une question....
Pourquoi tant de manipulations pour un résultat obtenu en 4 lignes? (et quasiment tout piloté à la souris... sauf le nommage des 2 dernières colonnes, manuellement inséré dans la barre de formule et le renommage des étapes)

let
  
    Source = Excel.CurrentWorkbook(){[Name="TB_AGENTS"]}[Content],
    FractV = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Dotation", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Dotation"),
    FractH = Table.SplitColumn(FractV, "Dotation", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Equipement accordé", "Désignation"}),
    Trim = Table.TransformColumns(FractH,{{"Equipement accordé", Text.Trim, type text}, {"Désignation", Text.Trim, type text}})
  
in
    Trim

Bonne journée

Re... là encore OUI !
mais dans ton code tout est DUR
Tu peux repartir de ce dernier et en faire une fonction qui répondra à tous les besoins.
En informatique il n'y a jamais de solutions uniques et selon les cas d'utilisations l'une peut être meilleure que l'autre ou inversement.