Power Query Import de valeurs registre

merinos-BernardEtang · 8 Novembre 2023

Bonjour,

je dois importer /- un million de lignes de données...Mais dans quelques mois se pourait être 10 millions de lignes. Donc la reponse doit être en PowerQuery.

Il s'agit de 4 registres pour une serie de compteurs. Un registre ne peut que monter quand de l'energie passe dedans.
Dans chaque fichier il y a plusieurs compteurs. (dans l'exemple je n'en ai gardé que quelques uns).

Mes données sont dans 400 fichiers qui ont tous la même structure.. donc en soi pas de probleme.

Je vous mets le resultat de l'import pour l'un d'entre eux... Traficoté de façon a ce que les valeurs soient bien visibles.

Ce que je cherche a obtenir c'est la quantité d'énergie (pas les valeurs de registres)

Je m'arrache un peu les cheveux depuis hier.

Merçi de votre aide...

Merinos

merinos-BernardEtang · 8 Novembre 2023

re bonjour a tous,

J'ai trouvé une methode qui permet de prendre sur une ligne les informations de ce qui est sur la ligne précédante...

Mais pas sûr que ce soit la méthode a employer.

Merinos

job75 · 8 Novembre 2023

Bonsoir merinos,

Au vu de l'image fournie il faut en effet remplacer chaque valeur de registre par la différence entre sa valeur et celle de la ligne précédente.

Avec 10 000 000 de lignes on peut stocker les données dans un fichier Texte.

A traiter par PQ ou par lecture/écriture séquentiels avec une macro.

A+

mromain · 8 Novembre 2023

Bonsoir merinos, job, le forum,

Ci-dessous une autre requête basée sur le tableau Table1 du fichier que tu as fourni en exemple au post #1.
La requête fait une jointure sur elle-même pour récupérer la ligne précédente d’un même compte.
Pas sûr que ce soit plus performant que ta proposition au post #2 mais on sait jamais…

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TriParCptEtDate = Table.Sort(Source,{{"CPT ID", Order.Ascending}, {"date", Order.Ascending}}),
    AjoutColonneIndex0 = Table.AddIndexColumn(TriParCptEtDate, "Index0", 0, 1, Int64.Type),
    AjoutColonneIndex1 = Table.AddIndexColumn(AjoutColonneIndex0, "Index1", 1, 1, Int64.Type),
    FusionRequête = Table.NestedJoin(AjoutColonneIndex1, {"CPT ID", "Index0"}, AjoutColonneIndex1, {"CPT ID", "Index1"}, "DonnéesPrécédentes", JoinKind.LeftOuter),
    RecordDonnéesPrécédentes = Table.TransformColumns(FusionRequête,{{"DonnéesPrécédentes", Table.First, type record}}),
    ColonneAjoutéeRegistre1 = Table.AddColumn(RecordDonnéesPrécédentes, "Registre1", each [Registre 1] - (if [DonnéesPrécédentes] = null then [Registre 1] else [DonnéesPrécédentes][Registre 1])),
    ColonneAjoutéeRegistre2 = Table.AddColumn(ColonneAjoutéeRegistre1, "Registre2", each [Registre 2] - (if [DonnéesPrécédentes] = null then [Registre 2] else [DonnéesPrécédentes][Registre 2])),
    ColonneAjoutéeRegistre3 = Table.AddColumn(ColonneAjoutéeRegistre2, "Registre3", each [Registre 3] - (if [DonnéesPrécédentes] = null then [Registre 3] else [DonnéesPrécédentes][Registre 3])),
    ColonneAjoutéeRegistre4 = Table.AddColumn(ColonneAjoutéeRegistre3, "Registre4", each [Registre 4] - (if [DonnéesPrécédentes] = null then [Registre 4] else [DonnéesPrécédentes][Registre 4])),
    ColonnesSélectionnées = Table.SelectColumns(ColonneAjoutéeRegistre4,{"CPT ID", "date", "Registre1", "Registre2", "Registre3", "Registre4"})
in
    ColonnesSélectionnées

A+

merinos-BernardEtang · 8 Novembre 2023

Merci @mromain ,

Cela produit exactement ce dont j'ai besoin...
J'ai mis ton query dans le document... parfait.

J'étudierai ta solution demain.

Encore MERCI

Merinos

merinos-BernardEtang · 8 Novembre 2023

@mromain

Tout le truc est dans la ligne:

Code:

RecordDonnéesPrécédentes = Table.TransformColumns(ExpandedCustom,{{"D2", Table.First, type record}}),

Je me demande s'il n'est pas plus rapide de selectionner une ligne plutôt que de faire une jointure.
Cela passe biensûr par un groupement d'abord.

Je vais tester.

Merci

job75 · 8 Novembre 2023

Pour ceux qui préfèrent le VBA voyez cette macro :

VB:

Sub Fichiers_Textes()
Dim t#, chemin$, source$, destination$, ncol%, x1%, x2%, n%, i&, texte$, s2, memo, j%, s1, nlig&
t = Timer
chemin = ThisWorkbook.Path & "\"
source = Dir(chemin & "*.txt")
destination = "Fichier destination.txt"
ncol = 6
x1 = FreeFile
Open chemin & destination For Output As #x1
While source <> ""
    If source <> destination Then
        x2 = FreeFile
        Open chemin & source For Input As #x2
        n = n + 1
        i = 0
        While Not EOF(x2)
            i = i + 1
            Line Input #x2, texte
            s2 = Split(texte, vbTab)
            memo = s2
            If i > 1 Then
                If s2(0) = s1(0) Then 'si le texte en 1ère colonne ne change pas
                    For j = 2 To ncol - 1
                        s2(j) = s2(j) - s1(j)
                    Next j
                    texte = Join(s2, vbTab)
                End If
            End If
            s1 = memo
            Print #x1, texte 'transfert
            nlig = nlig + 1
        Wend
        Close #x2 'ferme le fichier source
    End If
    source = Dir
Wend
Close #x1 'ferme le fichier destination
MsgBox n & " fichier(s) et " & nlig & " lignes traités en " & Format(Timer - t, "0.00 \sec")
End Sub

Les 400 fichiers sources sont supposés être tous des fichiers Textes.

Edit : en fait il ne faut pas modifier les valeurs des registres quand le texte en 1ère colonne change.

chris · 9 Novembre 2023

Bonjour

J'avais fait quelques essais à un moment et conclu que le double index et la jointure était plus rapide que la référence à la ligne par une autre méthode...

merinos-BernardEtang · 9 Novembre 2023

Bonjour a Tous
(@chris @mromain )

La Methode "Merino2" est un essai par ligne... Pour l'instant elle semble plus rapide...
Demain je lance cela sur les gros volumes. Je vous tiens au jus.

@job75 : Merci de la methode. Cela sera peut-être utile si je dois reformater les fichiers d'origine.
Malheureusement je dois faire des analyses sur les 10.000.000 de lignes... donc les avoir en mémoire en une fois. Biensur on peut en profiter pour eliminer les lignes ou les 4 registres sont nulls...

Merci

job75 · 9 Novembre 2023

Bonjour merinos, mromain, chris, le forum,

En fait il ne faut pas modifier les valeurs des registres quand le texte en 1ère colonne change, j'ai donc corrigé mon post #7.

merinos à dit:
Biensur on peut en profiter pour eliminer les lignes ou les 4 registres sont nulls...

Pour cela voyez la macro de ce fichier (2) et la variable retenu.

A+

mromain · 9 Novembre 2023

Bonjour merinos, job, chris, le forum,

Il est possible de se passer de l'étape Table.TransformColumns(ExpandedCustom,{{"D2", Table.First, type record}}) avec la requête suivante :

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TriParCptEtDate = Table.Sort(Source,{{"CPT ID", Order.Ascending}, {"date", Order.Ascending}}),
    AjoutColonneIndex0 = Table.AddIndexColumn(TriParCptEtDate, "Index0", 0, 1, Int64.Type),
    AjoutColonneIndex1 = Table.AddIndexColumn(AjoutColonneIndex0, "Index1", 1, 1, Int64.Type),
    FusionRequête = Table.NestedJoin(AjoutColonneIndex1, {"CPT ID", "Index0"}, AjoutColonneIndex1, {"CPT ID", "Index1"}, "DonnéesPrécédentes", JoinKind.LeftOuter),
    ColonneAjoutéeRegistre1 = Table.AddColumn(FusionRequête, "Registre1", each try [Registre 1] - [DonnéesPrécédentes][Registre 1]{0} otherwise 0),
    ColonneAjoutéeRegistre2 = Table.AddColumn(ColonneAjoutéeRegistre1, "Registre2", each try [Registre 2] - [DonnéesPrécédentes][Registre 2]{0} otherwise 0),
    ColonneAjoutéeRegistre3 = Table.AddColumn(ColonneAjoutéeRegistre2, "Registre3", each try [Registre 3] - [DonnéesPrécédentes][Registre 3]{0} otherwise 0),
    ColonneAjoutéeRegistre4 = Table.AddColumn(ColonneAjoutéeRegistre3, "Registre4", each try [Registre 4] - [DonnéesPrécédentes][Registre 4]{0} otherwise 0),
    ColonnesSélectionnées = Table.SelectColumns(ColonneAjoutéeRegistre4,{"CPT ID", "date", "Registre1", "Registre2", "Registre3", "Registre4"})
in
    ColonnesSélectionnées

Vu que tu vas travailler sur un gros volume de données, peut-être que ça jouera...

Bonne journée

A+

merinos-BernardEtang · 9 Novembre 2023

Bonjour a Tous,

@job75 : pour finir c'est ta solution qui a fonctionné...
Première utilisation de VBA depuis longtemps...

Transformation des fichiers en VBA puis import des données propres par Query.

Merci a tous.

Merinos

job75 · 9 Novembre 2023

merinos à dit:
Transformation des fichiers en VBA puis import des données propres par Query

Puisqu'on utilise VBA pourquoi ne pas l'utiliser aussi pour importer les données ?

J'ai testé mon fichier (2) en répétant 20 000 fois le traitement séquentiel des 673 lignes :

- nombre de lignes traitées 13 460 000

- nombre de lignes retenues 2 480 000

- durée 81 secondes chez moi sur Win 11 Excel 2019.

Power Query va peut-être plus vite, de combien ?

merinos-BernardEtang · 9 Novembre 2023

power query permet d'employer power Pivot... et impossible de réaliser les mêmes choses autrement.

les données sont dans le document, mais pas dans des cellules.

calculer les croissances a 12 mois de décalage, super simple.
Créer des pivots sur des tables liées, impossible hors PP...

Power Query Import de valeurs registre

XLDnaute Accro

Pièces jointes

XLDnaute Accro

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Accro

Pièces jointes

XLDnaute Accro

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Accro

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

Discussions similaires

Partager cette page

Statistiques des forums