Microsoft 365 Relevé de Température

Roll · 8 Décembre 2022

Bonjour le forum,
J'extrait d'un fichier csv de prise de Température de différents camion frigorifiques.
J'arrive à un tableau (via Power Query) que vous trouverez dans le fichier ci-joint.
Le relevé de Temp est mis sur 2 lignes mais je voudrais que tout soit mis sur une seule ligne
Comment faire dans Power query?
Je vous joins le fichier pour exemple.

Hasco · 8 Décembre 2022

Bonjour,

Voir les requêtes dans le fichier ci-joint.

La première groupe par fournisseur et par date puis les tables sont divisée en tables de 2 lignes , triées par type de ligne et transposée

VB:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Type ligne" = Table.AddColumn(Source, "Type ligne", each try Number.From(Value.Is(Number.From([Column1.12]), type number)) otherwise 0),
    Bufferisé = Table.Buffer(#"Type ligne"),
    #"Lignes groupées" = Table.Group(Bufferisé, {"Fourn", "Date"}, {{"Données", each _, type table [Fourn=nullable text, Date=nullable datetime, Column1.12=nullable text, Type ligne=number]}}),
    #"Groupes Transformés" = Table.AddColumn(#"Lignes groupées", "Transformation",
            each Table.FromRecords(List.Transform(Table.Split(Table.SelectColumns(_[Données],{"Column1.12","Type ligne"}),2),
                        each Table.Transpose(
                                Table.Sort(_,{"Type ligne",Order.Ascending}),{"Column1.12","Température"}){0}))),
    #"Transformation développé" = Table.ExpandTableColumn(#"Groupes Transformés", "Transformation", {"Column1.12", "Température"}, {"Column1.12", "Température"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Transformation développé",{"Données"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Température", type number}})
in
    #"Type modifié1"

La seconde, donne un type de ligne (0 ou 1, température ou texte) et un index de ligne calculé
le tableau est trié sur ces deux nouvelles colonnes puis groupé.
Un nouvel enregistrement est créer pour chaque table à partir de ses deux lignes.

VB:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Personnalisée ajoutée" = Table.AddColumn(Source, "Type ligne", each try Number.From(Value.Is(Number.From([Column1.12]), type number)) otherwise 0),
    #"Index ajouté" = Table.AddIndexColumn(#"Personnalisée ajoutée", "Index", 0, 1, Int64.Type),
    Personnalisé1 = Table.TransformColumns( #"Index ajouté",{"Index", each Number.IntegerDivide(_,2)}),
    #"Lignes triées" = Table.Sort(Personnalisé1,{{"Index", Order.Ascending}, {"Type ligne", Order.Ascending}}),
    #"Lignes groupées" = Table.Group(#"Lignes triées", {"Index"}, {{"Datas", each _, type table [Fourn=text, Date=text, Column1.12=text, Type ligne=number, Index=number]}}),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Lignes groupées", "Personnalisé", each [fourn = [Datas]{0}[Fourn],Date = [Datas]{0}[Date],Column1.12= [Datas]{0}[Column1.12],Temp =  [Datas]{1}[Column1.12]]),
    #"Personnalisé développé" = Table.ExpandRecordColumn(#"Personnalisée ajoutée1", "Personnalisé", {"fourn", "Date", "Column1.12", "Temp"}, {"fourn", "Date", "Column1.12", "Temp"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisé développé",{"Index", "Datas"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Temp", type number}})
in
    #"Type modifié"

Ce qui aurait été bien c'est de voir votre requête originale pour le faire en une seule requête.

Cordialement

Hasco · 8 Décembre 2022

Re,

Une troisième requête par typage/indexation/tri/décalage de ligne :

VB:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Personnalisée ajoutée" = Table.AddColumn(Source, "Type ligne", each try Number.From(Value.Is(Number.From([Column1.12]), type number)) otherwise 0),
    #"Index ajouté" = Table.AddIndexColumn(#"Personnalisée ajoutée", "Index", 0, 1, Int64.Type),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Index ajouté", "Ligne", each Number.IntegerDivide([Index],2)),
    #"Lignes triées" = Table.Sort(#"Personnalisée ajoutée1",{{"Ligne", Order.Ascending}, {"Type ligne", Order.Ascending}}),
    Personnalisé1 = Table.FromColumns(Table.ToColumns(#"Lignes triées") & {List.Skip(#"Lignes triées"[Column1.12],1)& {null}},Table.ColumnNames(#"Lignes triées") & {"Temp"}),
    #"Lignes filtrées" = Table.SelectRows(Personnalisé1, each ([Type ligne] = 0)),
    #"Type modifié" = Table.TransformColumnTypes(#"Lignes filtrées",{{"Temp", type number}}),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Type modifié",{"Fourn", "Date", "Column1.12", "Temp"})
in
    #"Autres colonnes supprimées"

Un fois le bon ordre de tri obtenu, l'étape "Personnalisé1" recréer la table en lui adjoignant une colonne Temp. Les valeurs sont obtenues par décalage d'une ligne vers le haut des valeurs de la colonne "Column1.12".
ensuite les lignes de type 1 sont supprimées par filtrage

Et une quatrième façon de faire par fusion de deux tables qui sont les sous-sélections des lignes de type 1 pour l'une et des lignes de type 0 pour l'autre.
L'étape de tri n'est pas utile ici.

VB:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Personnalisée ajoutée" = Table.AddColumn(Source, "Type ligne", each try Number.From(Value.Is(Number.From([Column1.12]), type number)) otherwise 0),
    #"Index ajouté" = Table.AddIndexColumn(#"Personnalisée ajoutée", "Index", 0, 1, Int64.Type),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Index ajouté", "Ligne", each Number.IntegerDivide([Index],2)),
    #"Sous Tables fusionnées" = Table.NestedJoin( Table.SelectRows(#"Personnalisée ajoutée1",each [Type ligne] = 0), {"Ligne"}, Table.SelectRows(#"Personnalisée ajoutée1",each [Type ligne] = 1), {"Ligne"}, "Temp", JoinKind.LeftOuter),
    #"Temp développé" = Table.ExpandTableColumn(#"Sous Tables fusionnées", "Temp", {"Column1.12"}, {"Temp"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Temp développé",{{"Temp", type number}}),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Type modifié",{"Fourn", "Date", "Column1.12", "Temp"})
in
    #"Autres colonnes supprimées"

Cordialement

job75 · 9 Décembre 2022

Bonjour Roll, Hasco, le forum,

Une solution VBA classique pour importer un fichier CSV est de l'ouvrir en lecture séquentielle :

VB:

Sub Import_CSV()
Dim fichier$, x%, texte$, s, i&, a(), n&
fichier = ThisWorkbook.Path & "\Fichier CSV.csv" 'à adaptet au besoin
x = FreeFile
Open fichier For Input As #x 'ouverture en lecture séquentielle
Line Input #x, texte '1ère ligne (en-têtes)
While Not EOF(x)
    Line Input #x, texte
    s = Split(texte, ";")
    If i Mod 2 = 0 Then
        n = i / 2
        ReDim Preserve a(3, n) 'base 0
        a(0, n) = s(0)
        a(1, n) = Format(s(1), "mm/dd/yyyy hh:mm") 'date au format US
    End If
    If s(2) Like "*#*" Then a(3, n) = Val(Replace(s(2), ",", ".")) Else a(2, n) = s(2)
    i = i + 1
Wend
Close #x
'---restitution---
With [A2]
    .Resize(n + 1, 4) = Application.Transpose(a) 'Transpose est limitée à 65536 lignes
    .Offset(n + 1).Resize(Rows.Count - n - .Row, 4).ClearContents 'RAZ en dessous
End With
Columns("A:D").AutoFit 'ajustement largeurs
End Sub

Avec le chemin utilisé les 2 fichiers zippés joints doivent être placés dans le même dossier (le bureau).

S'il y a plus de 65536 lignes restituées il faudra écrire un code pour la transposition.

A+

job75 · 9 Décembre 2022

Pour tester, sur le fichier CSV, j'ai recopié la plage A2:C23 sur 121 000 lignes.

La restitution se fait donc sur 60 500 lignes.

Chez moi sur Win 11 Excel 2019 la macro s'exécute en 1,7 seconde.

Roll · 11 Décembre 2022

Bonsoir,
merci pour ces réponses.
Je vais essayer de les appliquer dans mon fichier
Bonne fin de WE

Microsoft 365 Relevé de Température

Roll

XLDnaute Occasionnel

Pièces jointes

Hasco

XLDnaute Barbatruc

Pièces jointes

Hasco

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Roll

XLDnaute Occasionnel

Discussions similaires