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

[Henri69]

Bonjour,

J'ai un fichier de suivi de production journalier qui est une matrice avec :
- ligne = ref produit
- colonne = date du jour (1 colonne = 1 date).

Par contre je crois que ce format de table n'est pas directement exploitable dans power pivot, et je dois transformer cette matrice pour avoir une table avec en colonne la ref produit, la date puis la quantité. Cela implique de recréer une ligne pour chaque quantité saisie dans la matrice précédente... ce qui me semble assez complexe...

En PJ mon tableau actuel avec un onglet tableau cible.
2 questions :
- Existe-t-il une astuce pour que la matrice soit lisible directement dans Power Pivot (je ne pense pas mais je tente quand meme
- Et sinon savez vous comment je peux transformer ma table ?

Merci !

 

[Hasco]

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

 

[JHA]

Bonjour à tous,

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

JHA

 

[Hasco]

Hello @JHA

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

Cordialement

 

[JHA]

Bonjour @Hasco

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

JHA

 

[Hasco]

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

Pour une fois que ce n'est pas moi
Cordialement

 

[Henri69]

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 !

 

[JHA]

Bonjour à tous,

A essayer

JHA

 

[Henri69]

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 ?

 

[Hasco]

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

 

[merinos]

@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+

 

[Hasco]

On peut toujours tout imaginer.

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.

 

[merinos]

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.

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

 

[Henri69]

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 !

 

[Hasco]

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},_))}))

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"}})

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