Power Query Split des données Power query en différents onglet ou fichiers excel

jojolatribuche · 19 Décembre 2022

Bonjour la communauté.
J'ai un problème de taille.. et le temps est compté pfff
Je vous explique je dois communiquer pour nos clients leur nouvelles listes de prix pour 2023 la ou ça coince, c'est que j'ai une table avec 12 000 articles (sans la notion de client) et j'ai une table client qui en fonction de son groupe de prix ce voit attribuer ses prix.
Le soucis est que dans mon premier exemple pour le Groupe de prix PGGENESPW j'ai 1928 clients ce qui veux dire que actuellement dans Power Query j'ai fait une fusion de requête entre mes deux tables avec comme clé mon price groupe. Dans l'éditeur ça s'affiche (Génial) mais dans Excel non (Normal on est limité à un peu plus de 1 millions de lignes).
Ma question est donc est-il possible quand on est dans l'éditeur power query de lui donner comme instruction à chaque client qui trouve de créer soit un onglet excel ou encore mieux un fichier excel.
Je vous mets les captures d'écran car les fichiers de travail sont confidentielles :-(

La première capture est déjà le résultat de la fusion de la requête la première colonne est la clé qui va chercher dans la capture 2 la clé Price group.
Je lui demande de m'afficher le customer account que vous voyez en colonne 2 de la première capture d'écran.

Après ben bordel car cette requête comporte 14 millions de lignes à importer dans excel et ce n'est pas possible..
Donc à partir d'ici comment faire si c'est possible en fonction du Customer Account spliter cette requête en Fichier /onglet par client.. soit 1928 fichiers et ou onglet différents avec les 12000 articles

Merci de votre aide car je sèche et je suis pris par le temps dans la mesure ou je dois communiquer avant fin de semaine aux clients..

Cordialement

jojolatribuche · 19 Décembre 2022

chris à dit:
RE

Il y a combien de Price group ?

Car il faut une requête paramétrée par Price group et une boucle par client de chaque Price group
Ce qui est plus rapide qu'une requête par client

Une première ébauche qui ne tient pas compte du Price group

ça bug d'entrée sur articles.. query tables

vgendron · 19 Décembre 2022

Ca consiste en quoi justement la préparation des données??

jojolatribuche · 19 Décembre 2022

vgendron à dit:
Ca consiste en quoi justement la préparation des données??

si tu veux la table customer tables comporte tout nos clients (et j'ai l'information du price group) et de l'autre j'ai des fichiers d'analyse par price group que mes collègues on mis à jour mais on n'a pas la customer réf.
Ici dans l'exemple j'ai pris tout les customer client du price groupe PGGENSPW (qui fait déjà 12 millions de lignes) mais on a20 autres prices groupes (avec autant de clients ou bcp moins en fonction de son price groupes).. ce qui veux dire en l'état des choses si je lance ta macro il copie autant de fois qu'il y a de client sans se soucier tu price group...

vgendron · 19 Décembre 2022

il suffirait d'appliquer un filtre sur le pricrgroup. mais bon. j'ai l'impression d'aller à la pêche aux infos à chaque post et je pense qu'en plus le power query sera plus efficace et plus rapide. donc on va laisser tomber la macro.

jojolatribuche · 19 Décembre 2022

vgendron à dit:
il suffirait d'appliquer un filtre sur le pricrgroup. mais bon. j'ai l'impression d'aller à la pêche aux infos à chaque post et je pense qu'en plus le power query sera plus efficace et plus rapide. donc on va laisser tomber la macro.

ben au début je voulais travailler sur un price group et puis le déployer pour les autres et ne pas charger ma demande simplement..
Pour ça que je voulais passer par power query mais ca bloque déjà avec un price group et ses 1900 clients à l'affichage dans excel d'ou ma requête initiale et de pouvoir faire ce split au moment ou power query lie les infos client et son price group et les exportes par clients ensuite... mais sans devoir afficher les informations (qui d'ailleurs ne peux pas car trop de lignes) dans excel

b90coyote · 19 Décembre 2022

Sais-tu que tu n'est pas obligé d'afficher ton résultat dans ta feuille excel ?
il existe le choix d'affichage en "table" ou "créer uniquement la connexion"

Cela peut faire des requêtes intermédiaires utilisable via power query mais non affiché dans excel et donc qq fois de pouvoir traiter plus de données qu'excel n'en supporte.

chris · 19 Décembre 2022

jojolatribuche à dit:
ça bug d'entrée sur articles.. query tables
Regarde la pièce jointe 1158242

J'ai testé ce que j'ai posté : sur le fichier posté cela fonctionne et donne bien 30 fichiers Excel.

As-tu mis à jour le chemin (en jaune) vers les classeurs Customer Tables.xlsx et PGGENESPW.xlsx avant de tester ?
As-tu réglé tes paramètres POwerQuery :
Lancer PowerQuery, Fichier, Options et paramètres, Options de requête, partie GLOBAL : Confidentialité, Toujours ignorer les paramètres de niveau de confidentialité

Cela supprimera le message d'erreur s'il vient de cet absence de réglage.
Cette option par défaut de niveau de confidentialité ne sert à rien dans 99% des cas sauf à créer des problèmes

Mais comme déjà dit, cela ne sert à rien, pour un même Price Group, de rafraichir la requête puisque le fichier ne fait que restituer les mêmes données...

jojolatribuche · 20 Décembre 2022

chris à dit:
J'ai testé ce que j'ai posté : sur le fichier posté cela fonctionne et donne bien 30 fichiers Excel.

As-tu mis à jour le chemin (en jaune) vers les classeurs Customer Tables.xlsx et PGGENESPW.xlsx avant de tester ?
As-tu réglé tes paramètres POwerQuery :
Lancer PowerQuery, Fichier, Options et paramètres, Options de requête, partie GLOBAL : Confidentialité, Toujours ignorer les paramètres de niveau de confidentialité

Cela supprimera le message d'erreur s'il vient de cet absence de réglage.
Cette option par défaut de niveau de confidentialité ne sert à rien dans 99% des cas sauf à créer des problèmes

Mais comme déjà dit, cela ne sert à rien, pour un même Price Group, de rafraichir la requête puisque le fichier ne fait que restituer les mêmes données...

Premier test concluant (j'ai du mettre ' devant [articles].ListObject.QueryTable.Refresh et le reste des étapes s'effectue.
Maintenant quelques questions :-( sur Feuille 1 la colonne D et E si je fais actualiser il me mets le message suivant:

ensuite j'ai étais dans power query pour voir un peu le détail et voir un peu ton résonnement mais j'ai les erreurs suivantes que je n'arrive pas à corriger et ce pour les 3 tables.

Et dernière questions dans le fichier Feuille de travail on est bien d'accord que c'est ma table de mes 12000 articles que dois mettre là et non dans un fichier à part?

Sinon le fonctionnement ça fonctionne c'est déjà une belle étape maintenant comme tu disais plus haut ici on est juste sur un price group et pouvoir le faire sur l'emsemble des price groupe se serait top
@te lire mais déjà grand merci à toi

chris · 20 Décembre 2022

Bonjour

Ce message signifie que tu n'as pas réglé la confidentialité comme indiqué...

jojolatribuche · 20 Décembre 2022

chris à dit:
Bonjour

Ce message signifie que tu n'as pas réglé la confidentialité comme indiqué...

Voilà j'ai corrigé les coquilles lol
ça fonctionne type top sur un price groupe et l'ensemble de ses clients c'est formidables bravo et merci..
Puis-je encore abuser de tes connaissances et voir comment il est possible de faire cette opération sur l'ensemble des prices group? car ici comme expliqué au début on est sur un price group pggenespw mais l'idée que tu as suggéré serai de pouvoir choisir son price group et son fichier qui en découle maintenant comment faire. (Avoir des fichiers qu'il y a autant de price group ou un global ou je regroupe l'ensemble des prices group avec un filtre price group?
Je sais pas si je suis claire lol.. mais en tout cas top top top déjà merci

chris · 20 Décembre 2022

RE
J'ai

modifié Customer Tables.xlsx pour y ajouter des lignes
renommé PGGENESPW.xlsx en PGGENE2.xlsx et ajouté des lignes (dupliquées du 1er PG sauf la colonne PG) pour avoir 2 PG dans le fichier
modifié les requêtes, le paramétrage et le code VBA

Aucune idée de la durée sur ton volume...

Les 2 noms de fichier sont en dur dans les requêtes.
S'il faut choisir le Price group il faudra ajouter un paramètre et le gérer dans les requêtes...

jojolatribuche · 21 Décembre 2022

chris à dit:
RE
J'ai

modifié Customer Tables.xlsx pour y ajouter des lignes

renommé PGGENESPW.xlsx en PGGENE2.xlsx et ajouté des lignes (dupliquées du 1er PG sauf la colonne PG) pour avoir 2 PG dans le fichier

modifié les requêtes, le paramétrage et le code VBA

Aucune idée de la durée sur ton volume...

Les 2 noms de fichier sont en dur dans les requêtes.
S'il faut choisir le Price group il faudra ajouter un paramètre et le gérer dans les requêtes...

Je test et je te reviens merci bcp de ton support en tout cas tu me retire une fameuse épine du pieds..

jojolatribuche · 21 Décembre 2022

chris à dit:
RE
J'ai

modifié Customer Tables.xlsx pour y ajouter des lignes

renommé PGGENESPW.xlsx en PGGENE2.xlsx et ajouté des lignes (dupliquées du 1er PG sauf la colonne PG) pour avoir 2 PG dans le fichier

modifié les requêtes, le paramétrage et le code VBA

Aucune idée de la durée sur ton volume...

Les 2 noms de fichier sont en dur dans les requêtes.
S'il faut choisir le Price group il faudra ajouter un paramètre et le gérer dans les requêtes...

Bravo bravo bravo ce que tu m'a créé en quelques manipulations Powerquery et top je vais vraiment approfondir ce système car avec ce que tu as mis en place là ça m'ouvre d'autre perspective d'analyse.
Ceci étant dis puis-je encore abuser de ta gentille en ce qui concerne la macro qui fait le copier coller étant donné que tu la construite j'ai un peu de mal à lui faire dire la chose suivante.
Dans l'onglet Feuille de travail qui sert au copier coller j'aimerai qu'il exporte pas toutes les colonnes (La raison et que j'ai du lui rajouter une données de prix autre que celui des tables pour certain articles se sont des exceptions) et du coup j'ai des colonnes de formules que j'aimerai cacher au client lors du copier coller.
Serait'il possible de lui faire dire de copier seulement les colonnes B/C/D/L/M car ici j'ai simplement caché les colonnes mais au copier coller il les mets quand même...

Après ça je sais pas comment je peu te remercier si tu habitez pas loin je t'offrirai une bonne bouteille de vin pour te dire merci

chris · 21 Décembre 2022

Bonjour

Il y aurait sans doute des colonne à supprimer dans la requête si certaines ne servent pas

De même il serait préférable de gérer ces exceptions: on doit pouvoir coder les règles...

En l'état
Remplacer la partie client du code afin de masquer A, E à K, Nà Z, ne copier que ce qui est visible, démasquer ensuite

VB:

        For Each Client In [ClientsTous].ListObject.ListColumns("Customer account").DataBodyRange
            fichierXLS = Client & ".xlsx"
            Range("A:A,E:K,N:Z").EntireColumn.Hidden = True
            Range("Articles[#All]").SpecialCells(xlCellTypeVisible).Copy
            Workbooks.Add
            Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
                Cells.EntireColumn.AutoFit
            ActiveSheet.Name = Client
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs Chemin & fichierXLS
            ActiveWorkbook.Close
            Application.DisplayAlerts = True
            Cells.EntireColumn.Hidden = False

        Next Client

jojolatribuche · 21 Décembre 2022

chris à dit:

Bonjour

Il y aurait sans doute des colonne à supprimer dans la requête si certaines ne servent pas

De même il serait préférable de gérer ces exceptions: on doit pouvoir coder les règles...

En l'état
Remplacer la partie client du code afin de masquer A, E à K, Nà Z, ne copier que ce qui est visible, démasquer ensuite

VB:

        For Each Client In [ClientsTous].ListObject.ListColumns("Customer account").DataBodyRange
            fichierXLS = Client & ".xlsx"
            Range("A:A,E:K,N:Z").EntireColumn.Hidden = True
            Range("Articles[#All]").SpecialCells(xlCellTypeVisible).Copy
            Workbooks.Add
            Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
                Cells.EntireColumn.AutoFit
            ActiveSheet.Name = Client
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs Chemin & fichierXLS
            ActiveWorkbook.Close
            Application.DisplayAlerts = True
            Cells.EntireColumn.Hidden = False

        Next Client

Ca bug

Si tu veux pour certain clients (377 clients par rapport aux prix qui ce trouve dans la table des articles ont leur attribues un prix pour achat en grande quantité du coup j'ai créé un onglet avec cette table et j'ai fait un lien avec formule (RechercheV, SI.conditions etc ) pour avoir le prix final à communiquer au client soit de la table prix et pour certain articles d'un client spécifique le prix normal et ce fameux prix.
Du coup j'ai rajouté quelques colonnes mais qui doivent pas être visible.
Je t'ai envoyé en MP un lien drive google avec le fichier car il est gros 2Mo et avec les vraies données.
Si plus simple pour toi pour corriger la macro..
Merci merci merci

Power Query Split des données Power query en différents onglet ou fichiers excel

jojolatribuche

XLDnaute Junior

jojolatribuche

XLDnaute Junior

vgendron

XLDnaute Barbatruc

jojolatribuche

XLDnaute Junior

vgendron

XLDnaute Barbatruc

jojolatribuche

XLDnaute Junior

b90coyote

XLDnaute Nouveau

chris

XLDnaute Barbatruc

jojolatribuche

XLDnaute Junior

chris

XLDnaute Barbatruc

jojolatribuche

XLDnaute Junior

chris

XLDnaute Barbatruc

Pièces jointes

jojolatribuche

XLDnaute Junior

jojolatribuche

XLDnaute Junior

chris

XLDnaute Barbatruc

jojolatribuche

XLDnaute Junior

Discussions similaires