Discussion :

[JuliaButillon]

Bonjour,

J'aurais besoin d'un peu d'aide pour filtrer des données en fonction de dates.
J'ai un tableau de bord reprenant ma liste de projets, avec pour chaque projet plusieurs dates de paiement attendus et le montant effectivement reçu (sachant qu'il y a plusieurs dates et plusieurs montants pour un même projet).
J'aimerais faire ressortir rapidement les projets pour lesquels la date de paiement attendu est échue, mais pour lequel aucun montant n'a été reçu.

Mon tableau se présente comme suit :
Colonne A = Nom du projet
Colonne B = Date de paiement attendu 1
Colonne C = Montant effectivement reçu 1
Colonne D = Date de paiement attendu 2
Colonne E = Montant effectivement reçu 2
Colonne F = Date de paiement attendu 3
Colonne G = Montant effectivement reçu 3
Etc., jusqu'à 25 paiements.
Sachant que :
- Tous les projets n'ont pas forcément 25 paiements, donc il y a des cellules vides
- Les dates sont dans l'ordre chronologique pour un même projet, mais les différents projets peuvent avoir des dates totalement différentes
- Il y a à la fois des dates échues et des dates futures pour un même projet.

Exactement, ce que je voudrais faire c'est :
- Identifier quelle est la date échue la plus proche
- Analyser le montant reçu pour cette date et filtrer en fonction du résultat (je ne garde que les montants = 0).

J'ai essayé avec la formule MAX.SI.ENS, mais je ne pense pas qu'elle soit appropriée. Egalement essayé par PowerQuery, avec la fonction List.Min(List.Select), mais ça ne fonctionne pas non plus. J'ai envisagé un TCD, mais je ne vois pas dans quel sens le prendre :-).

Si quelqu'un a une idée, ça m'aiderait beaucoup !
(en espérant que mes explications sont assez claires !).

Merci d'avance,

[mromain]

Bonjour JuliaButillon,

Tu trouveras un exemple PowerQuery ci-dessous.
L'idée consiste à "transformer" ton tableau de 1+25x2 colonnes en un tableau à 3 colonnes pour pouvoir ensuite facilement filtrer.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
let
    SampleSource =
        let
            SampleTxtTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5dDBCoAgDIDhV4mdDdzU0rfoLh67hBREvX9haAh1TUgYwu9p+6yFYV2mcWsQGBAn2XI8B5gSsWVoSbH70AbZvHv/08exBEMJRoTDNc8hutQ6dPHdv6MRkFNQojA3xfWDVBuOhHcMrALjkUVBjmCo/HofY7gD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nom du projet = _t, Date de paiement attendu 1 = _t, Montant effectivement reçu 1 = _t, Date de paiement attendu 2 = _t, Montant effectivement reçu 2 = _t, Date de paiement attendu 3 = _t, Montant effectivement reçu 3 = _t, Date de paiement attendu 4 = _t, Montant effectivement reçu 4 = _t, Date de paiement attendu 5 = _t, Montant effectivement reçu 5 = _t, Date de paiement attendu 6 = _t, Montant effectivement reçu 6 = _t, Date de paiement attendu 7 = _t, Montant effectivement reçu 7 = _t, Date de paiement attendu 8 = _t, Montant effectivement reçu 8 = _t, Date de paiement attendu 9 = _t, Montant effectivement reçu 9 = _t, Date de paiement attendu 10 = _t, Montant effectivement reçu 10 = _t, Date de paiement attendu 11 = _t, Montant effectivement reçu 11 = _t, Date de paiement attendu 12 = _t, Montant effectivement reçu 12 = _t, Date de paiement attendu 13 = _t, Montant effectivement reçu 13 = _t, Date de paiement attendu 14 = _t, Montant effectivement reçu 14 = _t, Date de paiement attendu 15 = _t, Montant effectivement reçu 15 = _t, Date de paiement attendu 16 = _t, Montant effectivement reçu 16 = _t, Date de paiement attendu 17 = _t, Montant effectivement reçu 17 = _t, Date de paiement attendu 18 = _t, Montant effectivement reçu 18 = _t, Date de paiement attendu 19 = _t, Montant effectivement reçu 19 = _t, Date de paiement attendu 20 = _t, Montant effectivement reçu 20 = _t, Date de paiement attendu 21 = _t, Montant effectivement reçu 21 = _t, Date de paiement attendu 22 = _t, Montant effectivement reçu 22 = _t, Date de paiement attendu 23 = _t, Montant effectivement reçu 23 = _t, Date de paiement attendu 24 = _t, Montant effectivement reçu 24 = _t, Date de paiement attendu 25 = _t, Montant effectivement reçu 25 = _t]),
            ColsTransformations = List.Combine(List.Transform({1 .. 25}, each {{"Date de paiement attendu "&Text.From(_), (t) => Date.FromText(t, "fr-FR"), type date}, {"Montant effectivement reçu "&Text.From(_), Number.From, type number}}))
        in
            Table.Buffer(Table.TransformColumns(SampleTxtTable, ColsTransformations)),
    ListColumnsGroups = List.Transform({1 .. 25}, each {"Nom du projet", "Date de paiement attendu "&Text.From(_), "Montant effectivement reçu "&Text.From(_)}),
    CombineColumnsGroups = Table.Combine(List.Transform(ListColumnsGroups, each Table.RenameColumns(Table.SelectColumns(SampleSource, _), List.Zip({_, {"Nom du projet", "Date de paiement attendu", "Montant effectivement reçu"}})))),
    FilterMissingDeadlines = Table.SelectRows(CombineColumnsGroups, each ([Date de paiement attendu] < Date.From(DateTime.LocalNow())) and ([Montant effectivement reçu] = null))
in
    FilterMissingDeadlines

A+

[Raccourcix]

Bonjour

2 autres approches pour dépivoter des données en conservant la 1ere colonne et en prenant les autres colonnes 2 par 2

avec List.TransformMany - nécessite de connaitre la taille du tableau (ici 25 couples de colonnes)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Table.FromRows(
        List.TransformMany(
            Table.ToRows(Votre_Source), 
            each {1..25}, 
            (x,y) => {x{0}, x{2*y-1}, x{2*y}}),
        {"Nom du projet", "Date de paiement attendu", "Montant effectivement reçu"})

ou avec List.Split
avec 1 car on veux conserver une colonne au début
et 2 car on découpe chaque ligne 2 colonnes par 2 colonnes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
= Table.FromRows(
    List.Combine(
       Table.ToList(
            Votre_Source,
            (x) => List.Transform(
                     List.Split(List.RemoveFirstN(x, 1), 2),
                     (y) => List.FirstN(x, 1) & y))),
    {"Nom du projet", "Date de paiement attendu", "Montant effectivement reçu"})

Le principe reste le même que celui proposé par mromain

Stéphane

[JuliaButillon]

Bonjour à vous deux,
Merci infiniment pour votre aide !
Je n'avais pas du tout pensé à dépivoter les données. ça me sera même utile dans d'autres classeurs je pense.

En tout cas vraiment un grand merci !

Et belle journée,

Julia