Microsoft 365 Power pivot/query - transformation données - passer d'une matrice de quantité à une table par operation

Hasco

XLDnaute Barbatruc

Repose en paix

• 24 Février 2022

• #2

Bonjour,

Voir la requête dans le fichier joint (si je ne me suis pas trompé de tableau source).

Dans la mesure du possible, éviter power pivot dans excel si vous pouvez faire autrement. Power Pivot alourdit et ralentit les fichiers.
Vous pourrez peut-être utiliser uniquement PQ.

cordialement

 

Pièces jointes

• suivi prod journalier 2.xlsx

  36.3 KB · Affichages: 5

Vote positif 0 Vote négatif

J

JHA

XLDnaute Barbatruc

• 24 Février 2022

• #3

Bonjour à tous,

Un essai en dépivotant les colonnes des dates avec Power Query

JHA

 

Pièces jointes

• suivi prod journalier 2.xlsx

  30.8 KB · Affichages: 6

Vote positif 0 Vote négatif

Hasco

XLDnaute Barbatruc

Repose en paix

• 24 Février 2022

• #4

Hello @JHA

N'est-ce pas ce que j'ai fait ?

Cordialement

 

Vote positif 0 Vote négatif

J

JHA

XLDnaute Barbatruc

• 24 Février 2022

• #5

Bonjour @Hasco

Désolé, je n'ai pas fait attention avant de poster.

JHA

 

Vote positif 0 Vote négatif

Hasco

XLDnaute Barbatruc

Repose en paix

• 24 Février 2022

• #6

JHA à dit:

Désolé, je n'ai pas fait attention avant de poster.

Cliquez pour agrandir...

Pour une fois que ce n'est pas moi
Cordialement

 

Vote positif 0 Vote négatif

H

Henri69

XLDnaute Nouveau

• 25 Février 2022

• #7

Un grand merci, c'est exactement ca !
Comme ca semble un peu trop facile j'essaye de faire la même chose mais avec 2 critères pour dépivoter, et donc la création de 3 colonnes : date - objectif/réalisé - quantité. Pensez vous que ce soit possible ou il faut en amont que je fasse 2 tables distinctes (objectif / réalisé).

Merci pour votre aide !

 

Pièces jointes

• suivi prod journalier 3 - Copie.xlsx

  13.4 KB · Affichages: 8

Vote positif 0 Vote négatif

J

JHA

XLDnaute Barbatruc

• 25 Février 2022

• #8

Bonjour à tous,

A essayer

JHA

 

Pièces jointes

• suivi prod journalier 3 - Copie.xlsx

  25.9 KB · Affichages: 8

Vote positif 0 Vote négatif

H

Henri69

XLDnaute Nouveau

• 25 Février 2022

• #9

Merci, la méthode est donc de selectionner dans un 1er temps toutes les colonnes "objectif" et de les faire pivoter puis selectionner les colonnes "réalisé" et de les faire pivoter. Faire un peu de ménage en supprimant les lignes du haut inutile.
Cela permet de créer 4 colonnes, 2 colonnes avec la date, 1 colonne avec la quantité réalisé et une avec la quantité objectif.
Jusque la tout va bien. Par contre les 2 colonnes dates n'ont pas forcément les mêmes valeurs par ligne. Supprimer une colonne pour se baser uniquement sur l'autre ne marche donc pas je pense. Est ce ce cette manière que vous avez fait ou j'ai loupé une étape ?

 

Vote positif 0 Vote négatif

Hasco

XLDnaute Barbatruc

Repose en paix

• 25 Février 2022

• #10

Bonjour,

Voici une façon de faire en pivotant les colonnes.

On obtient une ligne par les réalisé et une ligne parles objectif

Puis vient la fusion des lignes de type 'objectif' et des lignes de type 'réalisé'.

La requête produit 36 lignes qui sont exactement le reflets de vos 72 valeurs divisée en 2

S'il faut produire des lignes pour les dates et produits n'ayant ni réalisés ni objectifs, il suffit de remplacer au départ toutes les valeurs nulles par des zéros on obtient alors 96 lignes

Dans le fichier vous avez les deux requêtes RQ_Datas et RQ_DatasZero

cordialement

 

Pièces jointes

• suivi prod journalier 4.xlsx

  35.6 KB · Affichages: 3

Vote positif 0 Vote négatif

merinos-BernardEtang

XLDnaute Accro

• 25 Février 2022

• #11

@Hasco ,

Ta methode fonctionne si on a toujours 2 colonnes pas date. ET qu'elles sont rangées.

et si on avait un objectif pour le dimanche et que l'on n'a pas pu ouvrir? on aurait un décalage...

j'ai pensé a une methode sans cet apriori


A+

 

Pièces jointes

• suivi prod journalier 3 - Copie.xlsx

  31.3 KB · Affichages: 3

Vote positif 0 Vote négatif

Hasco

XLDnaute Barbatruc

Repose en paix

• 25 Février 2022

• #12

On peut toujours tout imaginer.

Cliquez pour agrandir...

Mais ce qui m'intéressait, puisqu'il faut toujours justifier de ce qu'on fait, était de traiter le cas de deux lignes par produits et de fusionner les lignes sans ajouter les colonnes d'indexes habituelles.

J'ai réussit ses deux objectifs et suis content de ce que j'ai produit.

Maintenant que cela ne suffise pas, m'est assez égal en fait, je ne suis pas au travail, n'ai pas d'objectif à réaliser et n'ai rien à prouver à quiconque.

Et que ceux qui veulent faire mieux fassent mieux. Et laissent leur petits copains tranquilles.

 

Dernière édition: 25 Février 2022

Vote positif 0 Vote négatif

merinos-BernardEtang

XLDnaute Accro

• 25 Février 2022

• #13

Maintenant que cela ne suffise pas, m'est assez égal en fait, je ne suis pas au travail, n'ai pas d'objectif à réaliser et n'ai rien à prouver à quiconque.

Cliquez pour agrandir...

C'est évidant... Et j'ai fort apprécié ta facon de lire le contenu de la premiere ligne pour le remettre en titre...

 

Vote positif 0 Vote négatif

H

Henri69

XLDnaute Nouveau

• 25 Février 2022

• #14

Bonjour,

Merci à tout les 2 ! J'avoue que je suis plus à l'aise avec la méthode de Hasco qui fait parfaitement le job ! (J'ai du mal à comprendre la méthode avec l'index :/)

Seul hic, je ne comprends pas comment fonctionne le renommage de toutes les colonnes en même temps... Possible d'avoir un peu plus de détails pour la mettre en oeuvre ?

Encore merci !

 

Vote positif 0 Vote négatif

Hasco

XLDnaute Barbatruc

Repose en paix

• 25 Février 2022

• #15

Re,

voici une tentative d'explication de l'étape :

Table.RenameColumns(Source, let Noms = List.Range(Table.ColumnNames(Source),2) in
List.Zip({ Noms, List.Transform(Noms,each Text.Start(_,10) & "-" & Record.Field(Source{0},_))}))

Cliquez pour agrandir...

Il faut garder en tête que Power query travaille beaucoup sur les listes.


Table.RenameColumns attends en deuxième argument une liste de listes :

Table.RenameColumns(#"Type modifié",{{"Column1", "Nom1"},{"Column2", "Nom2"}})

Cliquez pour agrandir...

Chaque sous liste contenant un nom actuel de colonne et nouveau nom a lui attribuer

Pour nous les noms à changer sont tous ceux de la table SAUF les deux premiers, ce que nous donne :

List.Range(Table.ColumnNames(Source),2) et qui servira comme liste des noms actuels

Les nouveaux noms devront se présenter sous la forme "date" (de l'ancien nom) et soit "objectif" soit "réalisé", valeurs extraites de la première ligne de la table brute de transformation.

Cette liste de nouveaux noms est obtenue par transformation de la liste des noms actuels en une nouvelle liste :
List.Transform(Noms ,each Text.Start(_,10) & "-" & Record.Field(Source{0},_))

Qui peut se lire : pour chaque item de la liste des noms actuels, prend les 10 premiers caractères ajoute un tiret et la valeur correspondante de la première ligne de la source.
Valeur correspondante qui s'obtient pas 'Record.Field(Source{0},_)' où '_' est l'item parcouru de la liste des noms actuel et Source{0} est la première ligne de donnée de l'étape Source.

Une fois obtenues nos deux listes de noms List.Zip les compressera en une liste de sous-listes :
{
{Nom actuel 1, Nouveau nom 1} ,
{Nom actuel 2, Nouveau nom 2} ,
{Nom actuel 3, Nouveau nom 3}
}

C'est une étape qu'il faut écrire manuellement dans la barre de formule de PQ ou dans l'éditeur avancé.
Voici ce que je peux vous dire. N'hésitez pas en cas de besoin d'information complémentaire sur un point particulier.

Dans le fichier joint vous verrez une méthode (RQ_Datas (2)) faite par l'interface mais en plus d'étapes.

L'étape 'Noms' a été modifiée dans la barre de formule pour ajouter le List.Zip

Cordialement

 

Pièces jointes

• suivi prod journalier 4.xlsx

  41.3 KB · Affichages: 7

Dernière édition: 25 Février 2022

Vote positif 0 Vote négatif