Power Query Import de valeurs registre

merinos

XLDnaute Accro
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...
1699460711431.png


Merinos
 

Pièces jointes

  • analyse Sibe.xlsx
    78.5 KB · Affichages: 6
Solution
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...

merinos

XLDnaute Accro
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
 

Pièces jointes

  • PQ lien ligne precedante.xlsx
    32.2 KB · Affichages: 4

job75

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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

XLDnaute Accro
@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

XLDnaute Barbatruc
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.
 

Pièces jointes

  • Fichier source.txt
    50.5 KB · Affichages: 1
  • Pilote(1).xlsm
    18.9 KB · Affichages: 0
Dernière édition:

merinos

XLDnaute Accro
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
 

Pièces jointes

  • analyse Sibe.xlsx
    95 KB · Affichages: 1

job75

XLDnaute Barbatruc
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.
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+
 

Pièces jointes

  • Fichier source.txt
    50.5 KB · Affichages: 4
  • Pilote(2).xlsm
    19.5 KB · Affichages: 3

mromain

XLDnaute Barbatruc
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+
 

job75

XLDnaute Barbatruc
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

XLDnaute Accro
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...
 

Membres actuellement en ligne

Statistiques des forums

Discussions
313 276
Messages
2 096 760
Membres
106 744
dernier inscrit
sshkm16