Power Query Fractionner et modifier les colonnes d'un tableau

Créer une discussion
Répondre

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
M

Manu Bores

XLDnaute Nouveau
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 :
1750526265341.png

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.
 

Pièces jointes

Trier par date Trier par votes
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+
 
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
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+
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
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
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
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.
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
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+
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
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 ?
 
Vote positif 0 Vote négatif
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.
 
Dernière édition:
Vote positif 0 Vote négatif
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)
Cliquez pour agrandir...
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+
 

Pièces jointes

Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

M
  • Résolu(e)
XL 2019 Requête Power Query, positionnement en dessous
Réponses
12
Affichages
246
Maxinet
M
S
  • Question Question
XL 2019 power query
2
Réponses
16
Affichages
1 K
st007
S
L
  • Question Question
Microsoft 365 mise en forme fichier excel power query
Réponses
1
Affichages
204
vgendron
vgendron
L
  • Résolu(e)
Microsoft 365 power query help!!!
Réponses
7
Affichages
587
lilie17
L
F
  • Question Question
XL 2021 Power Query : extraire tableaux et données de PDF
Réponses
0
Affichages
261
Florent74
F
F
  • Résolu(e)
Power Query Power Query : chercher si une colonne existe et la renommer
Réponses
7
Affichages
370
Florent74
F
L
  • Résolu(e)
Power Query problème avec power query
2
Réponses
16
Affichages
702
Linda42
L
F
  • Résolu(e)
XL 2021 VBA et Power Query : ecrire des requetes avec variables
2
Réponses
28
Affichages
2 K
Florent74
F
E
  • Résolu(e)
Microsoft 365 POWER QUERY Pivoter la colonne
Réponses
14
Affichages
530
eric72
E
N
  • Question Question
Power Query comment copier une colonne d'un tableau sur un autre dans power query
Réponses
4
Affichages
420
merinos-BernardEtang
merinos-BernardEtang
D
  • Résolu(e)
Microsoft 365 Délimiteur Power Query
Réponses
13
Affichages
884
Derrdio
D
O
  • Question Question
Power Query Convertir un tableau de colonnes en lignes avec plusieurs variables (power query)
Réponses
8
Affichages
865
oobiz
O
Y
  • Résolu(e)
XL 2013 Récupérer le contenu d'un Tableau dans un autre classeur via Power Query et non toute la feuille...
Réponses
4
Affichages
472
Yvan1
Y
M
  • Question Question
XL 2010 Récupération de données et les transformer avec Power Query ?
Réponses
3
Affichages
625
DJunqueira
D
S
  • Question Question
XL 2019 Modifier les "limitations/plages" d'une feuille
2
Réponses
15
Affichages
1 K
ST4423
S
oguruma
POWERQUERY :: Totaliser les colonnes d'un tableau - 3 méthodes
Réponses
0
Affichages
410
oguruma
oguruma
T
  • Résolu(e)
Power Query Pb car ma source change les intitulés des colonnes
Réponses
2
Affichages
725
toto89
T
M
  • Résolu(e)
Microsoft 365 Power Query - Regroupement date début/date de fin
Réponses
9
Affichages
966
Maxou69
M
J
  • Question Question
Power Query Liste déroulante Power Query pour afficher les données si reportant
Réponses
8
Affichages
1 K
jojolatribuche
J
B
  • Question Question
Power Query Ne plus ajuster la largeur des colonnes d'un tableau issu de Power query
Réponses
8
Affichages
4 K
Phil7012
P
Retour