XL 2019 power query

st007 · 24 Mai 2024

Bonjour,
Je traitais un fichier .bin avec power query, le .bin grandit toujours. Et power query ne parvient plus à "Fractionner la colonne par délimiteur"
Cela doit engendrer plus de 6 000 colonnes que je transposais, puis re fractionnais etc
Sur le même principe,la requête ci-dessous fonctionne lentement,mais sans soucis.
Quels solutions s'offrent à moi ?

VB:

let
    Source = Csv.Document(File.Contents("C:\build-v510.1.0.bin"),2,":id",ExtraValues.Ignore,1252),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Plage de lignes conservée" = Table.Range(#"Type modifié",36,1),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Plage de lignes conservée",{"Column1"}),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Colonnes supprimées", "Column2", Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3", "Column2.4", "Column2.5", "Column2.6", "Column2.7", "Column2.8", "Column2.9", "Column2.10", "Column2.11", "Column2.12", "Column2.13", "Column2.14", "Column2.15", "Column2.16", "Column2.17", "Column2.18", "Column2.19", "Column2.20", "Column2.21", "Column2.22", "Column2.23", "Column2.24", "Column2.25", "Column2.26", "Column2.27", "Column2.28", "Column2.29", "Column2.30", "Column2.31", "Column2.32", "Column2.33", "Column2.34", "Column2.35", "Column2.36", "Column2.37", "Column2.38", "Column2.39", "Column2.40", "Column2.41", "Column2.42", "Column2.43", "Column2.44", "Column2.45", "Column2.46", "Column2.47", "Column2.48", "Column2.49", "Column2.50", "Column2.51", "Column2.52", "Column2.53", "Column2.54", "Column2.55", "Column2.56", "Column2.57", "Column2.58", "Column2.59", "Column2.60", "Column2.61", "Column2.62", "Column2.63", "Column2.64", "Column2.65", "Column2.66", "Column2.67", "Column2.68", "Column2.69", "Column2.70", "Column2.71", "Column2.72", "Column2.73", "Column2.74", "Column2.75", "Column2.76", "Column2.77", "Column2.78", "Column2.79", "Column2.80", "Column2.81", "Column2.82", "Column2.83", "Column2.84", "Column2.85", "Column2.86", "Column2.87", "Column2.88", "Column2.89", "Column2.90", "Column2.91", "Column2.92", "Column2.93", "Column2.94", "Column2.95", "Column2.96", "Column2.97", "Column2.98", "Column2.99", "Column2.100", "Column2.101", "Column2.102", "Column2.103", "Column2.104", "Column2.105", "Column2.106", "Column2.107", "Column2.108", "Column2.109", "Column2.110", "Column2.111", "Column2.112", "Column2.113", "Column2.114", "Column2.115", "Column2.116", "Column2.117", "Column2.118", "Column2.119", "Column2.120", "Column2.121", "Column2.122", "Column2.123", "Column2.124", "Column2.125", "Column2.126", "Column2.127", "Column2.128", "Column2.129", "Column2.130", "Column2.131", "Column2.132", "Column2.133", "Column2.134", "Column2.135", "Column2.136", "Column2.137", "Column2.138", "Column2.139", "Column2.140", "Column2.141", "Column2.142", "Column2.143", "Column2.144", "Column2.145", "Column2.146", "Column2.147", "Column2.148", "Column2.149", "Column2.150", "Column2.151", "Column2.152", "Column2.153", "Column2.154", "Column2.155", "Column2.156", "Column2.157", "Column2.158", "Column2.159", "Column2.160", "Column2.161", "Column2.162", "Column2.163", "Column2.164", "Column2.165", "Column2.166", "Column2.167", "Column2.168", "Column2.169", "Column2.170", "Column2.171", "Column2.172", "Column2.173", "Column2.174", "Column2.175", "Column2.176", "Column2.177", "Column2.178", "Column2.179", "Column2.180", "Column2.181"}),
    #"Table transposée" = Table.Transpose(#"Fractionner la colonne par délimiteur"),
    #"Fractionner la colonne par délimiteur1" = Table.SplitColumn(#"Table transposée", "Column1", Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur1",{{"Column1.1", Int64.Type}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}}),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Type modifié1",{"Column1.1", "Column1.4"}),
    #"En-têtes promus" = Table.PromoteHeaders(#"Colonnes supprimées1", [PromoteAllScalars=true]),
    #"Type modifié2" = Table.TransformColumnTypes(#"En-têtes promus",{{"count_from", Int64.Type}, {"level", Int64.Type}, {"reward_type", type text}, {"reward_def_id", Int64.Type}, {"reward_amount", Int64.Type}, {"name_loca_key", type text}}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Type modifié2", {"name_loca_key"}, client_FR, {"Column1"}, "client_FR", JoinKind.LeftOuter),
    #"client_FR développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "client_FR", {"Column2"}, {"client_FR.Column2"}),
    #"Colonnes permutées" = Table.ReorderColumns(#"client_FR développé",{"count_from", "client_FR.Column2", "level", "reward_type", "reward_def_id", "reward_amount", "name_loca_key"}),
    #"Colonnes supprimées2" = Table.RemoveColumns(#"Colonnes permutées",{"name_loca_key"}),
    #"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Colonnes supprimées2", "Personnalisé", each if [reward_def_id] = 1 then "Sifflet" else if [reward_def_id] = 2 then "Gemme" else if [reward_type] = "decoration" then "Décoration" else if [reward_type] = "flag" then "Drapeau" else if [reward_type] = "locomotive" then "Locomotive" else if [reward_type] = "wagon" then "Wagon" else null),
    #"Colonne fusionnée insérée" = Table.AddColumn(#"Colonne conditionnelle ajoutée", "Fusionné", each Text.Combine({Text.From([reward_amount], "fr-FR"), " ", [Personnalisé]}), type text),
    #"Colonnes supprimées3" = Table.RemoveColumns(#"Colonne fusionnée insérée",{"Personnalisé", "reward_amount", "reward_type"}),
    #"Colonnes permutées1" = Table.ReorderColumns(#"Colonnes supprimées3",{"count_from", "level", "client_FR.Column2", "reward_def_id", "Fusionné"}),
    #"Colonnes renommées" = Table.RenameColumns(#"Colonnes permutées1",{{"count_from", "Nb Loco"}, {"level", "Niveau"}, {"client_FR.Column2", "Nom"}}),
    #"Colonnes permutées2" = Table.ReorderColumns(#"Colonnes renommées",{"Nb Loco", "Niveau", "Nom", "Fusionné", "reward_def_id"}),
    #"Colonnes renommées1" = Table.RenameColumns(#"Colonnes permutées2",{{"Fusionné", "Récompense"}})
in

Cousinhub · 24 Mai 2024

Bonjour,

st007 à dit:
Quels solutions s'offrent à moi ?

Sans doute, plusieurs.
Mais la principale, peut-être un tout petit fichier exemple, exempt de toutes données confidentielles, mais avec des données représentatives...

st007 · 24 Mai 2024

re,voici un zip d'une partie du fichier .bin
déjà merci
donc, j'ouvre query, import de fichier csv, conserve une seule ligne.
et cette ligne devient un tableau de "^" colonnes sur "~" lignes

Cousinhub · 24 Mai 2024

Re-,
Juste un petit essai, non finalisé, mais déjà, tu dis

PowerQuery:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\cousi\Documents\Excel\exemples\PQ\csv\build-v507.0.0.bin"), null, null, 1252)}),
    #"Conserver les premières lignes" = Table.FirstN(Source,1),
    #"Fractionner la colonne par délimiteur" = Table.ExpandListColumn(Table.TransformColumns(#"Conserver les premières lignes", {{"Column1", Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Fractionner la colonne par délimiteur1" = Table.SplitColumn(#"Fractionner la colonne par délimiteur", "Column1", Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15"}),
    #"En-têtes promus" = Table.PromoteHeaders(#"Fractionner la colonne par délimiteur1", [PromoteAllScalars=true])
in
    #"En-têtes promus"

Pense à modifier le chemin dans la Source

st007 · 24 Mai 2024

excellent, j'ai beaucoup simplifié l'exemple, mais çà tourne super.
je cherche les différences .......par rapport à ce que j'avais

VB:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\Alain\Downloads\trainstation sheets\build-v512.1.0.bin"), null, null, 1252)}),

    #"Plage de lignes conservée" = Table.Range(Source,5,1),
    #"Fractionner la colonne par délimiteur" = Table.ExpandListColumn(Table.TransformColumns(#"Plage de lignes conservée", {{"Column1", Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Fractionner la colonne par délimiteur1" = Table.SplitColumn(#"Fractionner la colonne par délimiteur", "Column1", Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24"}),
    #"En-têtes promus" = Table.PromoteHeaders(#"Fractionner la colonne par délimiteur1", [PromoteAllScalars=true])
in
    #"En-têtes promus"

st007 · 24 Mai 2024

l'option avancée "en ligne" change la vie ....

st007 · 24 Mai 2024

il reste, que le copier coller fonctionne par l'éditeur avancé, mais qu'excel ne semble plus capable de la créer

Cousinhub · 24 Mai 2024

Si, ça le fait, mais il faut attendre un peu (beauoup???)
J'ai eu aussi ce laps de temps lors de mon essai
une petite astuce, après la Source, tu fais comme moi, tu ne gardes que la 1ère ligne, puis tu déroules tout le reste
et à la fin, tu supprimes cette étape (Table.FirstN(Source,1))

Cousinhub · 25 Mai 2024

Bonjour,
Autre petite astuce (hélas non pérenne sous 2016 et antérieure, mais normalement valide sous 2019)
Enlever le typage automatique
Dans "Données/Obtenir des données/Options de requête (tout en bas)"
Mettre comme ceci au chargement :

Le typage peut se faire à tout moment, dès lors qu'on en a besoin.
Bonne journée

st007 · 26 Mai 2024

merci,merci
je garde une ligne différente par requête,
et en pratique, j'utilise ces requêtes après chaque nouveau fichier .bin.
Je maintiens ainsi la mise en forme, même si pour chaque requête, je re valide les étapes à roues dentées.
il faudrait presque limiter l'actualisation dans power query à une dizaine de ligne, pour la méthodologie, et tout considérer au"fermer et charger"

Cousinhub · 26 Mai 2024

Bonjour,
En regardant plus finement ton fichier .bin, j'ai vu qu'il y avait 3 types différents :
- building:id
- blocker:id
- flag:id
Et chaque type a ses titres de colonne différents.
Est-ce toujours le cas (uniquement 3 types), ou chaque fichier bin peut comporter un nombre différent?
Auquel cas, il faut transposer avant de fractionner la colonne par lignes avec délimiteur "~"
Et ensuite fractionner chaque colonne en colonnes avec délimiteur "^"
Tu obtiendras ainsi 3 tableaux en 1
Par ailleurs, je ne comprends pas ton "re valide les étapes à roues dentées"
Edit :
Sinon, tu peux te créer un fichier de base, avec une cellule prévue pour y mettre l'adresse et le nom du fichier bin, comme ça, un "Actualiser" suffira, sans nécessiter l'édition du code..
Peut-être?

st007 · 30 Mai 2024

Bonjour,
J'ai supprimé 90% du .bin pour pouvoir le poster.
A l'ouverture par pq, j'ai 2 colonnes et 61 lignes (fixe)
J'ai une requête pour garder les ligne 3, 6, 10, 20 sur 4 onglets
Le nombre de types est fixe (61 lignes), le nombre de colonnes de titre aussi (2).
Donc source - le fichier
plage de ligne conservée (disons la 3è)
je ne garde que "column2"
fractionner par délimiteur ~ en ligne (là, le nombre de lignes obtenues évolue à chaque nouveau bin)
fractionner par délimiteur ^ la ,le nombre de colonnes est fixe

Ca, c'est le "formattage de base".
A réception d'un nouveau .bin, j'édite en cliquant sur la roue dentée (dans les étapes appliquées)pour définir le nouveau .bin.
en modifiant les paramètres de pq, types automatique, actualisation , l'option avancée du "fractionner la colonne" en ligne ont considérablement allégé le délai de traitement.
Tu envisagerais sur une feuille du fichier une cellule comportant " l'adresse" du .bin " passée en parametre pour les requétes.
l'idée serait géniale, mais il faut que je creuse pour la mettre en place

Cousinhub · 30 Mai 2024

Bonjour,
Je crois que le fichier .bin n'est pas passé
Peut-être en le zippant?

st007 · 30 Mai 2024

bonjour,
je n'ai pas essayé de faire passé un bin complet qui fait 8 mo, 1 mo avec 7zip, la méthodologie m'est plus importante et instructive

Cousinhub · 30 Mai 2024

Re-,
OK, cependant, on ne dispose pas de la requête "client_FR"
Et lorsqu'on déroule le code fourni au #1, on a des erreurs, les noms de colonne ne correspondent pas
Donc, pas simple de t'aider

XL 2019 power query

st007

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

st007

XLDnaute Barbatruc

Pièces jointes

Cousinhub

XLDnaute Barbatruc

st007

XLDnaute Barbatruc

st007

XLDnaute Barbatruc

st007

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

st007

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

st007

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

st007

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Discussions similaires