Power Query Fractionner et modifier les colonnes d'un tableau

Manu Bores · 21 Juin 2025

Bonjour,
Au travail, j'ai un logiciel qui exporte dans Excel des listes de factures sous un forme totalement inexploitable.
Voici un aperçu de l'export :

Je voudrais obtenir 3 colonnes pour chaque article : "Eau Part fixe.HT", "Eau Part fixe.TVA", "Eau Part fixe.TTC". Idem pour les colonnes suivantes.
Dans Power Query, je sais fractionner une colonne par délimiteur (ici le délimiteur est le saut de ligne), puis renommer la colonne et la nettoyer pour ne garder que les nombres. Mais je suis obligé de recommencer pour chaque colonne.
De plus, d'un export à l'autre, je n'ai pas le même nombre de colonnes à fractionner, et les colonnes n'ont pas toujours le même nom.
J'arrive à récupérer la liste des entêtes de colonnes que je veux fractionner (avec la fonction Table.ColumnNames), mais je ne parviens pas à utiliser cette liste comme paramètre dans ma requête.

Comment automatiser ce travail, pour obtenir une requête capable de transformer mes différents exports ?
Merci de votre aide

Ci-joint un exemple de fichier Excel à retravailler.

mromain · 22 Juin 2025

Bonsoir Manu Bores et bienvenue sur le forum,

Voici un essai à adapter. L'étape pathExcelFile est à adapter avec l'emplacement du fichier source :

PowerQuery:

let
    pathExcelFile = "C:\...\Etat_Recouvrement-Perso.xlsx",
    SourceExcelFile = Excel.Workbook(File.Contents(pathExcelFile), null, true),
    SourceTableau1 = SourceExcelFile{[Item="Tableau1",Kind="Table"]}[Data],
    listColumns = {"Eau Part fixe", "Eau Part variable", "Assainissement Part fixe", "Assainissement Part variable", "Redevance Eau", "AC", "Eau", "Total"},
    fnSplitValues = (v as text) as nullable record => if v = "" then null else List.Accumulate(Text.Split(v, "#(lf)"), [], (s, c) => let split = Text.Split(c, " : ") in Record.AddField(s, split{0}, Number.FromText(Text.Replace(split{1}, " €", ""), "fr-FR"))),
    TransformColumns = Table.TransformColumns(SourceTableau1, List.Transform(listColumns, each {_, fnSplitValues, type record})),
    ExpandColumns = List.Accumulate(listColumns, TransformColumns, (s, c) => Table.ExpandRecordColumn(s, c, {"HT", "TVA", "TTC"}, {c & ".HT", c & ".TVA", c & ".TTC"}))
in
    ExpandColumns

A+

Cousinhub · 22 Juin 2025

Bonjour,
@mromain , très joli code, mais si je peux me permettre...
La conversion en nombre, au format "français", provoque des erreurs dans les nombres comportant un espace insécable ou un espace simple comme séparateur de milliers.
En remplaçant "fr-FR" par "af-ZA", la conversion ne pose plus de problèmes (j'ai fait un petit tuto à ce sujet ICI)
Bon dimanche

Manu Bores · 22 Juin 2025

mromain à dit:

Bonsoir Manu Bores et bienvenue sur le forum,

Voici un essai à adapter. L'étape pathExcelFile est à adapter avec l'emplacement du fichier source :

PowerQuery:

let
    pathExcelFile = "C:\...\Etat_Recouvrement-Perso.xlsx",
    SourceExcelFile = Excel.Workbook(File.Contents(pathExcelFile), null, true),
    SourceTableau1 = SourceExcelFile{[Item="Tableau1",Kind="Table"]}[Data],
    listColumns = {"Eau Part fixe", "Eau Part variable", "Assainissement Part fixe", "Assainissement Part variable", "Redevance Eau", "AC", "Eau", "Total"},
    fnSplitValues = (v as text) as nullable record => if v = "" then null else List.Accumulate(Text.Split(v, "#(lf)"), [], (s, c) => let split = Text.Split(c, " : ") in Record.AddField(s, split{0}, Number.FromText(Text.Replace(split{1}, " €", ""), "fr-FR"))),
    TransformColumns = Table.TransformColumns(SourceTableau1, List.Transform(listColumns, each {_, fnSplitValues, type record})),
    ExpandColumns = List.Accumulate(listColumns, TransformColumns, (s, c) => Table.ExpandRecordColumn(s, c, {"HT", "TVA", "TTC"}, {c & ".HT", c & ".TVA", c & ".TTC"}))
in
    ExpandColumns

A+

Merci beaucoup, @mromain , pour ce code. Exactement ce que je cherchais à faire. J'ai juste modifié la ligne listColumns pour chercher trouver dynamiquement le nom des champs à traiter, et ça marche sur tous mes fichiers !
Et je suis bluffé par le petit nombre de lignes.
En fait, je n'ai pas compris grand-chose aux 3 dernières expressions, mais c'est efficace !
Moi, j'utilise la grosse artillerie du débutant 🙂
Encore merci.

Manu Bores · 22 Juin 2025

Cousinhub à dit:
Bonjour,
@mromain , très joli code, mais si je peux me permettre...
La conversion en nombre, au format "français", provoque des erreurs dans les nombres comportant un espace insécable ou un espace simple comme séparateur de milliers.
En remplaçant "fr-FR" par "af-ZA", la conversion ne pose plus de problèmes (j'ai fait un petit tuto à ce sujet ICI)
Bon dimanche

Très intéressant. Dans mes fichiers source, il y a justement l'espace insécable comme séparateur de milliers. Je les traitais par un rechercher-remplacer. Je vais changer de méthode.
Merci bien.

job75 · 23 Juin 2025

Bonsoir à tous,

Voyez le fichier joint et cette macro :

VB:

Sub Eclater()
Dim col%, lig&, x$, p%
Application.ScreenUpdating = False
With [Tableau1].ListObject 'tableau structuré
    .TableStyle = "TableStyleMedium3" 'le style que vous voulez
    With .Range
        If Application.CountIf(.Rows(2), "HT") Then Exit Sub 'la macro ne sert qu'une fois sur le tableau
        .Rows(1).Insert xlDown: .Rows(1).Copy .Rows(0): .Rows(1).Hidden = True 'insère la nouvelle ligne de titres
        .Rows(2).Insert xlDown: .Rows(0).EntireRow.Copy .Rows(2): .Rows(2).ClearContents 'insère la ligne des sous-titres
        For col = .Columns.Count To 4 Step -1
            .Columns(col).Resize(, 3).EntireColumn.Insert 'insère 3 colonnes
            .Cells(0, col).Resize(, 3).Merge: .Cells(0, col) = .Cells(1, col + 3)
            .Cells(2, col).Resize(, 3) = Array("HT", "TVA", "TTC")
            For lig = 3 To .Rows.Count
                x = .Cells(lig, col + 3)
                p = InStr(x, "HT"): If p Then .Cells(lig, col) = Val(Replace(Replace(Replace(Mid(x, p + 2), Chr(160), ""), ":", ""), ",", "."))
                p = InStr(x, "TVA"): If p Then .Cells(lig, col + 1) = Val(Replace(Replace(Replace(Mid(x, p + 3), Chr(160), ""), ":", ""), ",", "."))
                p = InStr(x, "TTC"): If p Then .Cells(lig, col + 2) = Val(Replace(Replace(Replace(Mid(x, p + 3), Chr(160), ""), ":", ""), ",", "."))
            Next lig
            .Columns(col + 3).EntireColumn.Delete
        Next col
        '---formats---
        With Union(.Rows(0), .Cells)
            .Borders.Weight = xlThin
            For col = 1 To .Columns.Count Step 3: .Columns(col).Resize(, 3).BorderAround Weight:=xlMedium: Next col
        End With
        .Rows(0).Resize(3, 3).Borders(xlInsideHorizontal).Color = .Rows(0).Interior.Color 'pour masquer la bordure
        With Union(.Cells(0, 4), .Columns(4)).Resize(, .Columns.Count - 3)
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 12.5
            .NumberFormat = "#,##0.00 €"
        End With
        .Rows("3:" & .Rows.Count).AutoFit 'ajustement hauteurs
        With .Parent.UsedRange: End With 'ajuste les barres de défilement
        With ActiveWindow: .FreezePanes = False: .SplitRow = 4: .FreezePanes = True: End With 'fige les volets
        .Rows(0).Select: ActiveWindow.Zoom = True 'ajuste le zoom
        Application.Goto .Cells(0, 1), True 'cadrage
    End With
End With
End Sub

Elle ne doit être exécutée qu'une fois, sur un nouveau tableau.

Edit : j'ai peaufiné le formatage.

Bonne nuit.

bsalv · 23 Juin 2025

le niveau de dépivoter, cela dépend du but, (TCD?)...

job75 · 23 Juin 2025

Bonjour le forum,

Il vaut mieux 2 feuilles avec une macro évènementielle dans la feuille "Traitement".

J'ai ajouté aussi un tableau VBA pour gagner du temps :

VB:

Private Sub Worksheet_Activate()
Dim col%, i&, tablo, x$, p%
Application.ScreenUpdating = False
Sheets("Import").Cells.Copy Cells(1) 'copier-coller de toute la feuille
With ListObjects(1) 'tableau structuré
    .TableStyle = "TableStyleMedium3" 'le style que vous voulez
    With .Range
        .Rows(1).Insert xlDown: .Rows(1).Copy .Rows(0): .Rows(1).Hidden = True 'insère la nouvelle ligne de titres
        .Rows(2).Insert xlDown: .Rows(0).EntireRow.Copy .Rows(2): .Rows(2).ClearContents 'insère la ligne des sous-titres
        For col = .Columns.Count To 4 Step -1
            .Columns(col).Resize(, 3).EntireColumn.Insert 'insère 3 colonnes
            .Cells(0, col).Resize(, 3).Merge: .Cells(0, col) = .Cells(1, col + 3)
            .Cells(2, col).Resize(, 3) = Array("HT", "TVA", "TTC")
            tablo = .Columns(col).Resize(, 4) 'matrice, plus rapide
            For i = 3 To UBound(tablo)
                x = tablo(i, 4)
                p = InStr(x, "HT"): If p Then tablo(i, 1) = Val(Replace(Replace(Replace(Mid(x, p + 2), Chr(160), ""), ":", ""), ",", "."))
                p = InStr(x, "TVA"): If p Then tablo(i, 2) = Val(Replace(Replace(Replace(Mid(x, p + 3), Chr(160), ""), ":", ""), ",", "."))
                p = InStr(x, "TTC"): If p Then tablo(i, 3) = Val(Replace(Replace(Replace(Mid(x, p + 3), Chr(160), ""), ":", ""), ",", "."))
            Next i
            .Columns(col).Resize(, 3) = tablo 'restitution
            .Columns(col + 3).EntireColumn.Delete
        Next col
        '---formats---
        With Union(.Rows(0), .Cells)
            .Borders.Weight = xlThin
            For col = 1 To .Columns.Count Step 3: .Columns(col).Resize(, 3).BorderAround Weight:=xlMedium: Next col
        End With
        .Rows(0).Resize(3, 3).Borders(xlInsideHorizontal).Color = .Rows(0).Interior.Color 'pour masquer la bordure
        With Union(.Cells(0, 4), .Columns(4)).Resize(, .Columns.Count - 3)
            .HorizontalAlignment = xlCenter
            .ColumnWidth = 12.5
            .NumberFormat = "#,##0.00 €"
        End With
        .Rows("3:" & .Rows.Count).AutoFit 'ajustement hauteurs
        With .Parent.UsedRange: End With 'ajuste les barres de défilement
        .Rows(0).Select: ActiveWindow.Zoom = True 'ajuste le zoom
        Application.Goto .Cells(0, 1), True 'cadrage
    End With
End With
With ActiveWindow: .FreezePanes = False: .SplitRow = 3: .FreezePanes = True: End With 'fige les volets
End Sub

A+

job75 · 23 Juin 2025

Même avec le tableau VBA la macro précédente n'est pas très rapide.

Pour tester j'ai recopié le tableau source sur 9 000 lignes.

Chez moi l'activation de la feuille "Traitement" se fait en 48 secondes (58 secondes sans le tableau VBA).

Combien de temps avec Power Query ?

Cousinhub · 23 Juin 2025

job75 à dit:
Même avec le tableau VBA la macro précédente n'est pas très rapide.

Pour tester j'ai recopié le tableau source sur 9 000 lignes.

Chez moi l'activation de la feuille "Traitement" se fait en 48 secondes (58 secondes sans le tableau VBA).

Combien de temps avec Power Query ?

Hello,
A l'ouverture du fichier :

Et deuxième (une fois la connexion effective)

job75 · 23 Juin 2025

Merci Cousinhub, oui y a pas photo.

Notez quand même que la durée d'exécution se décompose comme suit :

- environ 9 secondes pour la préparation (copier-coller et insertion de 2 lignes)

- environ 12 secondes pour l'insertion des 24 colonnes

- environ 18 secondes pour la suppression des 8 colonnes

- environ 11 secondes pour le formatage.

bsalv · 23 Juin 2025

9000 lignes >>> dépivoté = 129.000 lignes en 5.5 sec + update TCD en 0.6 sec = 6.1 sec

bsalv · 24 Juin 2025

@job75, il y a des possibilités pour réduire cette macro vers la moitié du temps (si vous voulez)

job75 · 24 Juin 2025

Bonjour le forum,

bsalv à dit:
@job75, il y a des possibilités pour réduire cette macro vers la moitié du temps (si vous voulez)

Oui ça m'intéresse beaucoup si le résultat est bien celui que j'obtiens avec les formats.

Maintenant voici une version (2) qui convertit le tableau copié en plage et n'insère que 2 colonnes à chaque fois, il n'y a plus de suppression :

VB:

Private Sub Worksheet_Activate()
Dim plage As Range, col%, i&, tablo, x$, p%
Application.ScreenUpdating = False
Cells.Delete 'RAZ
Sheets("Import").ListObjects(1).Range.EntireColumn.Copy Cells(1) 'copier-coller du tableau structuré
With ListObjects(1) 'tableau structuré
    .TableStyle = "TableStyleMedium3" 'le style que vous voulez
    Set plage = .Range
    .Unlist 'convertit le tableau en plage
End With
With plage
    .Rows(2).Insert xlDown 'insère la ligne des sous-titres
    For col = .Columns.Count To 4 Step -1
        .Columns(col).Resize(, 2).EntireColumn.Insert 'insère 2 colonnes
        .Cells(1, col) = .Cells(1, col + 2): .Cells(1, col + 2) = ""
        .Cells(2, col).Resize(, 3) = Array("HT", "TVA", "TTC")
        tablo = .Columns(col).Resize(, 3) 'matrice, plus rapide
        For i = 3 To UBound(tablo)
            x = tablo(i, 3)
            p = InStr(x, "HT"): If p Then tablo(i, 1) = Val(Replace(Replace(Replace(Mid(x, p + 2), Chr(160), ""), ":", ""), ",", "."))
            p = InStr(x, "TVA"): If p Then tablo(i, 2) = Val(Replace(Replace(Replace(Mid(x, p + 3), Chr(160), ""), ":", ""), ",", "."))
            p = InStr(x, "TTC"): If p Then tablo(i, 3) = Val(Replace(Replace(Replace(Mid(x, p + 3), Chr(160), ""), ":", ""), ",", "."))
        Next i
        .Columns(col).Resize(, 3) = tablo 'restitution
    Next col
    '---formats---
    With .Columns(4).Resize(, .Columns.Count - 3)
        .HorizontalAlignment = xlCenter
        .ColumnWidth = 12.5
        .NumberFormat = "#,##0.00 €"
    End With
    .Borders.Weight = xlThin
    .Resize(1, 3).Borders(xlEdgeBottom).LineStyle = xlNone
    For col = 1 To .Columns.Count Step 3
        If col > 1 Then .Columns(col).Resize(1, 3).HorizontalAlignment = xlCenterAcrossSelection
        .Columns(col).Resize(, 3).BorderAround Weight:=xlMedium 'contour des 3 colonnes
    Next col
    .Rows.AutoFit 'ajustement hauteurs
    .Resize(2).RowHeight = 25 'titres et sous-titres
    With .Parent.UsedRange: End With 'ajuste les barres de défilement
    .Rows(1).Select: ActiveWindow.Zoom = True 'ajuste le zoom
    Application.Goto .Cells(1), True 'cadrage
End With
With ActiveWindow: .FreezePanes = False: .SplitRow = 2: .FreezePanes = True: End With 'fige les volets
End Sub

Sur 9 000 lignes la macro s'exécute chez moi en 31 secondes.

A+

bsalv · 24 Juin 2025

un essai, lancer la macro "M_Job75"

Power Query Fractionner et modifier les colonnes d'un tableau

Manu Bores

XLDnaute Nouveau

Pièces jointes

mromain

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Manu Bores

XLDnaute Nouveau

Manu Bores

XLDnaute Nouveau

job75

XLDnaute Barbatruc

Pièces jointes

bsalv

XLDnaute Occasionnel

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

bsalv

XLDnaute Occasionnel

bsalv

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Pièces jointes

bsalv

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

Power Query Fractionner et modifier les colonnes d'un tableau

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

Privacy & Transparency

Privacy & Transparency