Power Query Power query Soustraire 2 sommes dont une a été remplacé de null à 0

[guypont]

Bonjour,
Dans mon tableau je dois calculé l'évolution d'un mois sur l'autre de différents comptes
Pour certains comptes j'ai des valeurs à "0", pour d'autre il est indiqué "Null"
Pour les comptes avec "0", la soustraction se fait bien (exple Cptes 60376200, 606130000).
Pour les comptes avec "Null", je remplace cette valeur par "0", mais la soustraction ne se fait pas (Cptes 602225000, 602280,606130)

Quelle solution ?
merci

 

[alexga78]

Bonjour à tous,

Un essai 100% Power Query, sans vouloir alimenter la battle PQ <> VBA 🙂

let
A = Table.SelectRows(Table.TransformColumns(Excel.CurrentWorkbook(), {"Content", each Table.SelectRows(_, each not List.Contains({"",null}, [Compte]))}), each Text.Contains([Name], "Table")),
B = (x)=> Text.Proper(Date.ToText(Date.From(x), [Format = "MMMM", Culture="fr-FR"])),
C = {List.Transform({1..12}, each B(Text.From(_)&"/2026")), List.FirstN(Table.ColumnNames(A[Content]{0}),2)},
D = Table.TransformColumns(A, {"Content", each let x = List.Last(Table.ColumnNames(_)) in Table.RenameColumns(_, {x, B(x)})}),
E = let x = Table.Combine(D[Content]) in Table.Buffer(Table.SelectColumns(x, C{1} & List.Intersect({C{0}, Table.ColumnNames(x)}))),
F = Table.Sort(Table.Combine(Table.Group(E,
    C{1},
    {"x", each
        [ a = List.Transform(Table.ToColumns(_), each let x = List.Max(_) in if x = null then 0 else x),
          b = (x)=> List.FirstN(x,3) & List.Combine(List.Skip(List.Accumulate(List.Skip(x,3), {{x{2},null}}, (s,c)=> s & {{c, try c & " - " & List.Last(s){0} otherwise c - List.Last(s){0}}}))),
          c = Table.FromRows({b(a)},b(Table.ColumnNames(_)))
        ][c]
    })[x]), "Compte")
in F

Bonne journée à tous.

Edit: suppression des totaux dans les données source pour plus de rapidité de traitement

 

[guypont]

Re-,
Hélas non, l'étape "Combine" est entrée manuellement dans le barre de formules... (pour simplifier le code)
Le reste peut être effectuée à la souris, dans les différents rubans.

Bonjour,
Ma réponse d'hier a disparu.
Je te disais donc que j'allais utiliser ton fichier comme modèle de base

Merci beaucoup de ton aide

Bonne journée

 

[guypont]

En mémorisant la feuille normalement ce sera un peu plus rapide :

Private Sub Worksheet_Activate()
Dim An%, n%, nf$, feuilles(), ub%, d As Object, tablo, i&, x$, resu(), a, ii&
An = 26 'à adapter
'---liste des feuilles---
For n = 1 To 12
    On Error Resume Next
    nf = Format(n, "00") & An
    If IsError(Sheets(nf)) Then Else ub = ub + 1: ReDim Preserve feuilles(ub): feuilles(ub) = nf
Next n
'--- liste des comptes sans doublon---
Set d = CreateObject("Scripting.Dictionary")
For n = 1 To ub
    tablo = Sheets(feuilles(n)).ListObjects(1).Range 'tableau structuré
    For i = 2 To UBound(tablo)
        x = tablo(i, 1)
        If x <> "" Then If Not d.exists(x) Then d(x) = n 'mémorise la feuille
Next i, n
Application.ScreenUpdating = False
Rows("2:" & Rows.Count).ClearContents 'RAZ
If d.Count = 0 Then Exit Sub
'---tableau des résultats---
ReDim resu(1 To d.Count, 1 To 2 * ub + 1)
a = d.keys
For i = 1 To d.Count
    resu(i, 1) = a(i - 1)
    For n = d(resu(i, 1)) To ub
        tablo = Sheets(feuilles(n)).ListObjects(1).Range
        For ii = 2 To UBound(tablo)
            If tablo(ii, 1) = resu(i, 1) Then
                If resu(i, 2) = "" Then resu(i, 2) = tablo(ii, 2)
                resu(i, IIf(n = 1, 3, 2 * n)) = tablo(ii, 3)
                Exit For
            End If
Next ii, n, i
'---restitution et formules des évolutions---
[A2].Resize(d.Count, 2 * ub + 1) = resu
If ub > 1 Then [E2].Resize(d.Count) = "=RC[-1]-RC[-2]"
If ub > 2 Then
    For n = 3 To ub
        Cells(2, 2 * n + 1).Resize(d.Count) = "=RC[-1]-RC[-3]"
    Next n
End If
With UsedRange: End With 'actualise les barres de défilement
End Sub

Bonjour,

Merci de ta participation

Je ne me suis pas penché sur VBA car les lignes de commandes sont complexes et nécessitent donc une bonne connaissance du langage.

Power Query,et ses menus de fonction, est dans un peu plus accessible même si cela ne suffit pas toujours.

Bonne journée

 

[guypont]

Bonjour à tous,

Un essai 100% Power Query, sans vouloir alimenter la battle PQ <> VBA 🙂

let
A = Table.SelectRows(Table.TransformColumns(Excel.CurrentWorkbook(), {"Content", each Table.SelectRows(_, each not List.Contains({"",null}, [Compte]))}), each Text.Contains([Name], "Table")),
B = (x)=> Text.Proper(Date.ToText(Date.From(x), [Format = "MMMM", Culture="fr-FR"])),
C = {List.Transform({1..12}, each B(Text.From(_)&"/2026")), List.FirstN(Table.ColumnNames(A[Content]{0}),2)},
D = Table.TransformColumns(A, {"Content", each let x = List.Last(Table.ColumnNames(_)) in Table.RenameColumns(_, {x, B(x)})}),
E = let x = Table.Combine(D[Content]) in Table.Buffer(Table.SelectColumns(x, C{1} & List.Intersect({C{0}, Table.ColumnNames(x)}))),
F = Table.Sort(Table.Combine(Table.Group(E,
    C{1},
    {"x", each
        [ a = List.Transform(Table.ToColumns(_), each let x = List.Max(_) in if x = null then 0 else x),
          b = (x)=> List.FirstN(x,3) & List.Combine(List.Skip(List.Accumulate(List.Skip(x,3), {{x{2},null}}, (s,c)=> s & {{c, try c & " - " & List.Last(s){0} otherwise c - List.Last(s){0}}}))),
          c = Table.FromRows({b(a)},b(Table.ColumnNames(_)))
        ][c]
    })[x]), "Compte")
in F

Bonne journée à tous.

Edit: suppression des totaux dans les données source pour plus de rapidité de traitement

Bonjour,

Effectivement, le résultat est bien celui escompté mais Les lignes de commandes se rapprochent de VBA. Donc compliqué voire inaccessible pour un profane sauf à se contenter d'une simple copie.
Merci pour ton aide et je garde qd même ton fichier en réserve

Bonne journée pluvieuse

 

[Cousinhub]

Hello,
Je ne sais pas de quelle région "pluvieuse"
Mais même ici, en BZH, juste un peu de vent, et quelques gouttes....
Une dernière petite intervention de ma part, non pas pour alimenter une quelconque "battle PQ<>VBA", mais pour souligner le :

Notez cependant que VBA fait souvent les choses plus facilement,

Effectivement, ma proposition nécessitait "UNE" ligne de code manuelle, le reste s'effectuant avec une souris, et un doigt agile....
Mais cette intervention m'a bien fait rire
Bonne soirée, et bon W-E