Power Query Retravailler des données à l'origine en ligne pour les mettre en colonne

[Céline49]

Bonjour,

Je suis complètement débutant en Power Query et ne sait même pas si ce que je veux est réalisable ou pas.
J'ai des données dans 3 bases différentes (mes 3 1ers onglets). Je souhaite obtenir un tableau comme dans le dernier onglet qui me permets de mieux visualiser:
les dossiers avec les attributions par collaborateur d'une part et d'autre part à partir de la liste complète des dossiers, ceux qui ont eu des aides et ceux qui n'en ont pas eu (la liste complète des dossiers est en onglet "liste sylae" alors que ceux qui ont été aidés est dans l'onglet "liste avec aide") et surtout de visualiser les aides plutôt en colonnes plutôt qu'en ligne avec que ce soit plus lisibles. Cela aboutit donc pour tous les dossiers à avoir 4 lignes (toujours les mêmes: aide au paiement covid1 , aide au paiement covid 2, exonération covid 1, exonération covid 2)
Est-ce réalisable?
Merci par avance pour votre contribution

 

[Hasco]

Bonjour,

Voici une solution.
La liste des mois est établit à partir de la date minimale et la date maximale de la colonne 'Période'.
Cette liste est fusionnée ensuite avec les aides puis 'Pivotée'
La requête Résultat fusionne le tableau T_Base avec la liste des mois et leur résultat.
Pour le tri des quatre item à conserver, il faudra voir plus tard, je n'ai pas le temps ce soir, et serai absent toute la semaine. Mais je suis certain que d'autres pourront vous dépanner.
Vous pouvez éventuellement vous créer une liste personnalisée dans excel et vous en servir pour le tri (Options excel /Options avancées/modifier les listes personnalisées)

Remarquez le truc utilisé dans l'étape 'LesMois dévellopé' (Et oui PQ oublies les 's') pour qu'à chaque actualisation, la liste des noms de colonnes soit toujours adapter aux mois de la période.

{"Libellé"} & List.Range(Table.ColumnNames(LesMois),2,List.Count(Table.ColumnNames(LesMois))-2)

Cordialement

 

[chris]

Bonjour

On fait quoi avec Exonération tout court ?

 

[soan]

Bonjour chris, le fil,

On fait quoi avec Exonération tout court ?

euh... on paye rien du tout ? 😜 chouette ! 😇

(j'adore les exonérations, et tout c'qui est gratuit !)

soan

 

[Hasco]

On fait quoi avec Exonération tout court ?

Hello @chris 🙂 me suis posé la même question et... m'en suis tenu à la demande.🙂

j'adore les exonérations, et tout c'qui est gratuit !

Hello @soan 🙂 ..zet les gratifications. 🎂🥇🏆

cordialement

 

[chris]

RE à tous

Sorry je n'avais pas vu ton post #2

De mon côté J'ai les aides triées et seulement le s2 sociétés voulues mais pas les mois sans valeurs...

 

[job75]

Bonsoir Céline49, Roblochon, chris, soan,

Pour ceux qui restent fidèles au VBA, le code de la feuille "Résultat" :

Private Sub Worksheet_Activate()
Dim ncol%, deb As Date, dDate As Object, col%, d As Object, tablo, i&, x$, resu(), a, b, n&, dd As Object, lig&, s
ncol = 21 'nombre de colonnes du tableau des résultats
deb = CDate("1/2/2020") 'date de début
'---liste des dates---
Set dDate = CreateObject("Scripting.Dictionary")
For col = 7 To ncol
    dDate(DateSerial(Year(deb), Month(deb) + col - 7, 1)) = col 'mémorise la colonne
Next
'---liste des numéros dossier sans doublon---
Set d = CreateObject("Scripting.Dictionary")
With Sheets("liste avec aide")
    tablo = .Range(.ListObjects(1).Name).Resize(, 6) 'matrice, plus rapide
End With
For i = 1 To UBound(tablo)
    x = tablo(i, 1)
    If x <> "" Then d(x) = tablo(i, 2) 'mémorise la société
Next
If d.Count = 0 Then GoTo 1
ReDim resu(1 To 4 * d.Count, 1 To ncol) 'tableau des résultats
a = d.keys: b = d.items
'---résultats colonnes 1 2 6---
For i = 1 To UBound(resu) Step 4
    resu(i, 1) = a(n): resu(i, 2) = b(n): resu(i, 6) = "Aide au paiement Covid 1"
    resu(i + 1, 1) = a(n): resu(i + 1, 2) = b(n): resu(i + 1, 6) = "Exonération Covid 1"
    resu(i + 2, 1) = a(n): resu(i + 2, 2) = b(n): resu(i + 2, 6) = "Aide au paiement Covid 2"
    resu(i + 3, 1) = a(n): resu(i + 3, 2) = b(n): resu(i + 3, 6) = "Exonération Covid 2"
    n = n + 1
Next
'---résultats colonnes des dates---
Set dd = CreateObject("Scripting.Dictionary")
dd.CompareMode = vbTextCompare 'la casse est ignorée
For i = 1 To UBound(resu)
    dd(resu(i, 1) & resu(i, 6)) = i 'mémorise la ligne
Next
For i = 1 To UBound(tablo)
    x = tablo(i, 1) & Trim(tablo(i, 5))
    If dd.exists(x) Then
        lig = dd(x)
        col = dDate(tablo(i, 4))
        If col And IsNumeric(CStr(tablo(i, 6))) Then resu(lig, col) = resu(lig, col) + CDbl(tablo(i, 6))
    End If
Next
'---résultats colonnes 3 4 5---
With Sheets("liste avec coll")
    tablo = .Range(.ListObjects(1).Name).Resize(, 10) 'matrice, plus rapide
End With
For i = 1 To UBound(tablo)
    x = tablo(i, 1)
    If d.exists(x) Then d(x) = tablo(i, 7) & Chr(1) & tablo(i, 8) & Chr(1) & tablo(i, 10)
Next
For i = 1 To UBound(resu)
    s = Split(d(resu(i, 1)), Chr(1))
    resu(i, 3) = s(0): resu(i, 4) = s(1): resu(i, 5) = s(2)
Next
'---restitution---
1 Application.ScreenUpdating = False
On Error Resume Next
With Range(ListObjects(1).Name)
    .Delete xlUp 'RAZ
    .Resize(UBound(resu)) = resu
End With
End Sub

La macro se déclenche quand on active la feuille.

Elle est très rapide car elles utilise des tableaux VBA et 3 Dictionary.

La feuille "liste sylae" n'est pas utilisée.

A+

 

[Céline49]

Bonjour à vous tous
Roblochon... ça marche à peu près sauf l'actualisation de la liste des codes dossier. Quand on modifie et que j'actualise, je ne sais pas pourquoi ça ne s'actualise pas dans l'onglet "tableau souhaité"
Chris: Avec "Exonération" tout court, il s'agit du total des exonération Covid 1 + exonération Covid 2. Donc pour le tableau souhaité, on ne peut pas le réutiliser.