Transposer en colonnes des blocs de données en lignes - via PowerQuery

oguruma

XLDnaute Occasionnel
Suite et fin pour terminer ce billet sur le dégroupage en colonnes de données présentées en lignes qui fait référence au post ci-dessous.

Cette solution est à préférer aux 3 autres proposées. Elle a l'avantage d'une mise à jour plus dynamique en cas d'évolution de la source des données par la simple actualisation du tableau résultant.

Un peu de paramétrage et passage de paramètres à la requête... à voir pour les plus experts. cf. mes post sur le passage de paramètres dans une RQ powerquery
L'actualisation peut aussi se présenter via un bouton qui fera appel à du VBA pour actualiser la requêtes ou faire clic droit/actualiser sur le tableau...
A voir selon les besoins

PowerQuery:
let
    //======================================================================================================================
    // Auteur : OGURUMA
    // Dégroupage en colonne d'un bloc de données en ligne
    //======================================================================================================================
 
    //======================================================================================================================
    // Quelle est la source des données
    // changer la valeur de au besoin
    // Name="Tableau1" ==> peut-être obtenu par un passage de paramètres
    //======================================================================================================================
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],

    //======================================================================================================================
    // Les modifications de types ne sont peut-être pas nécessaire. A voir selon les besoins
    //======================================================================================================================
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Rubriques", type text}, {"Informations", type any}}),

    //======================================================================================================================
    // Création des colonnes conditionnelles qui constitueront les données du tableau dégroupé
    // Ici on crée les colonnes nécessaires qui seront retenues
    // A adapter selon les rubriques présentes
    // Le tableau initial contient là-ici deux colonnes Rubriques et Informations - à adapter selon la source
    //======================================================================================================================
    CODE_ARTICLE = Table.AddColumn(#"Type modifié", "CODE_ARTICLE", each if [Rubriques] = "CODE_ARTICLE" then [Informations] else null),
    ENTREPOT = Table.AddColumn(CODE_ARTICLE, "ENTREPOT", each if [Rubriques] = "ENTREPOT" then [Informations] else null),
    STOCK_MAXI = Table.AddColumn(ENTREPOT, "STOCK_MAXI", each if [Rubriques] = "STOCK_MAXI" then [Informations] else null),
    STOCK_MINI = Table.AddColumn(STOCK_MAXI, "STOCK_MINI", each if [Rubriques] = "STOCK_MINI" then [Informations] else null),
    STOCK_ACTUEL = Table.AddColumn(STOCK_MINI, "STOCK_ACTUEL", each if [Rubriques] = "STOCK_ACTUEL" then [Informations] else null),
    EN_COMMANDE = Table.AddColumn(STOCK_ACTUEL, "EN_COMMANDE", each if [Rubriques] = "EN COMMANDE" then [Informations] else null),
    GESTIONNAIRE = Table.AddColumn(EN_COMMANDE, "GESTIONNAIRE", each if [Rubriques] = "GESTIONNAIRE" then [Informations] else null),

    //======================================================================================================================
    // On aligne les données
    //======================================================================================================================
    REMPLI_VERS_HAUT = Table.FillUp(GESTIONNAIRE,{"ENTREPOT", "STOCK_MAXI", "STOCK_MINI", "STOCK_ACTUEL", "EN_COMMANDE", "GESTIONNAIRE"}),

    //======================================================================================================================
    // On se débarrase des valeurs nulles qui occasionne le décalage
    //======================================================================================================================
    SUPPR_NULL = Table.SelectRows(REMPLI_VERS_HAUT, each ([CODE_ARTICLE] <> null)),
 
    //======================================================================================================================
    // Les colonnes de référence qui ont permi la ventilation des données ne servent plus
    // Les colonnes à conserver seront à adapter selon la source
    // Ici on conserve les colonnes conditionnelles crées
    //======================================================================================================================
    SUPPR_COL_REFERENCE = Table.SelectColumns(SUPPR_NULL,{"CODE_ARTICLE", "ENTREPOT", "STOCK_MAXI", "STOCK_MINI", "STOCK_ACTUEL", "EN_COMMANDE", "GESTIONNAIRE"})
in
    SUPPR_COL_REFERENCE
 

Pièces jointes

  • Transpose_Blocs_Lignes_Colonnes_V4.xlsm
    57.7 KB · Affichages: 7

mromain

XLDnaute Barbatruc
Bonjour oguruma, le forum,

Ci-dessous une autre approche en PowerQuery :
PowerQuery:
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    RenameColumns = Table.RenameColumns(Source,{{"Rubriques", "Name"}, {"Informations", "Value"}}),
    SplitTable = Table.Split(RenameColumns, List.Count(List.Distinct(RenameColumns[Name]))),
    ConvertToRecord = List.Transform(SplitTable, Record.FromTable),
    ToTable = Table.FromRecords(ConvertToRecord)
in
    ToTable
  • l'étape RenameColumns sert juste à renommer les colonnes en Name et Value ce qui est utile pour la conversion de table en record un peu plus loin ;
  • l'étape SplitTable sert à splitter la table en "groupe de données" ;
  • l'étape ConvertToRecord sert à convertir chaque "groupe de données" en record ;
  • l'étape ToTable sert à convertir la liste de records en table.

A+
 

oguruma

XLDnaute Occasionnel
Bonjour oguruma, le forum,

Ci-dessous une autre approche en PowerQuery :
PowerQuery:
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    RenameColumns = Table.RenameColumns(Source,{{"Rubriques", "Name"}, {"Informations", "Value"}}),
    SplitTable = Table.Split(RenameColumns, List.Count(List.Distinct(RenameColumns[Name]))),
    ConvertToRecord = List.Transform(SplitTable, Record.FromTable),
    ToTable = Table.FromRecords(ConvertToRecord)
in
    ToTable
  • l'étape RenameColumns sert juste à renommer les colonnes en Name et Value ce qui est utile pour la conversion de table en record un peu plus loin ;
  • l'étape SplitTable sert à splitter la table en "groupe de données" ;
  • l'étape ConvertToRecord sert à convertir chaque "groupe de données" en record ;
  • l'étape ToTable sert à convertir la liste de records en table.

A+
Oui en effet, et j'avais déjà présenté des Tips un peu sous cette forme d'écriture voir-même en poussant plus loin on pourrait convertir ta version sous une fonction.
La solution que j'ai présentais s'adresse plus à des débutants en pwq car les lignes de codes ont été obtenues en utilisant les assistants de powerquery.
Cela consiste essentiellement à l'ajout de colonnes conditionnelles, aligner les données par un remplissage vers le haut par rapport à la 1er colonne de référence, se débrasser des Null sur la colonne de référence et ne conserver que les colonnes utiles. Tout ceci via les assistants.
Effectivement je préfère et de loin ta solution mais pour des néophytes c'est peut-être du haut vol car il faut déjà bien maîtriser sous pws les notions de list, record et table...
On devrait être en phase sur ce constat :)
merci pour ta réactivité.... ça alimente le sujet.
ps : c'était un truc que j'avais dans coin.... me suis dit... tiens je vais le mettre au gout du jour
 

Cousinhub

XLDnaute Barbatruc
Inactif
Bonjour,
Pour continuer d'alimenter le sujet, une variante
PowerQuery:
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Transpose = Table.FromRows(List.Split(Source[Informations],List.Count(List.Distinct(Source[Rubriques]))), List.Distinct(Source[Rubriques]))
in
    Transpose
Effectivement, l'idée de créer quelques exemples d'utilisation de PQ est excellente, et de voir différentes approches permet à tout le monde de se familiariser un peu plus avec l'outil
Bonne soirée à tous, passez de bonnes fêtes
 

oguruma

XLDnaute Occasionnel
Bonjour,
Pour continuer d'alimenter le sujet, une variante
PowerQuery:
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Transpose = Table.FromRows(List.Split(Source[Informations],List.Count(List.Distinct(Source[Rubriques]))), List.Distinct(Source[Rubriques]))
in
    Transpose
Effectivement, l'idée de créer quelques exemples d'utilisation de PQ est excellente, et de voir différentes approches permet à tout le monde de se familiariser un peu plus avec l'outil
Bonne soirée à tous, passez de bonnes fêtes
idem joyeux Noêl
 

oguruma

XLDnaute Occasionnel
Bonjour à tous, suite aux différentes méthodes PWQ je me suis livré à quelques tests de perf. Voici les résultats :

Test 1 - Volumétrie
1703171213482.png

1703171230755.png


PowerQuery
1703171239340.png


1703171271051.png


Solution de CousinHub impossible d'aller au bout.... Excel en Freeze.... obligé de tuer Excel

Test 2 - Volumétrie
1703171476554.png


1703171487945.png



1703171496875.png


1703171513301.png


Solution de CousinHub impossible d'aller au bout.... Excel en Freeze.... obligé de tuer Excel

Test 3 - Volumétrie

1703171591729.png


1703171607778.png


1703171617137.png



1703171633585.png


1703171642195.png



Constat toutes raisons gardées et respect pour la solution apportée.

La version de CousinHub :
Transpose = Table.FromRows(List.Split(Source[Informations],List.Count(List.Distinct(Source[Rubriques]))), List.Distinct(Source[Rubriques]))

certes économique en Nbr de lignes pour la maintenance la RQ essaie de monter tout en mémoire et effectue le découpage en une seule passe tandis que celle de mromain
SplitTable = Table.Split(RenameColumns, List.Count(List.Distinct(RenameColumns[Name]))),
ConvertToRecord = List.Transform(SplitTable, Record.FromTable),
ToTable = Table.FromRecords(ConvertToRecord)

inclu ces deux étapes supplémentaires ce qui permet éviter l'engorgement mémoire

Celle de Oguruma sur faible quantité mémoire en effectuant les montées et transposition pas à pas est presque coude à coude avec celle de Romain.

Cela dit sur une très faible volumétrie celle de CousinHub pourrait être avantageuse pour des raisons de qualité de maintenance qui se ferait sur deux lignes et totalement dynamique.

A étudier le pour et le contre de chacune des solutions :)

Petite précision : dans le code j'ai désactivé le calcul automatique ;)

ainsi que pour bien mesurer le temps de la RQ et affichage de la Dlg de fin quand la RQ est bien terminée afin de ne pas fausser les chiffres : pas traitement en arrière plan :)

1703172487916.png
 
Dernière édition:

Cousinhub

XLDnaute Barbatruc
Inactif
Bonsoir,
Comme tu disposes de ton fichier test, que donnerait ce code, éclaté, et où j'effectue les calculs en premier, afin de ne les faire qu'une fois?
PowerQuery:
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Col = List.Distinct(Source[Rubriques]),
    Nb = List.Count(Col),
    Split = List.Split(Source[Informations],Nb),
    From = Table.FromRows(Split,Col)
in
    From
Bonne soirée
 

oguruma

XLDnaute Occasionnel
Bonsoir,
Comme tu disposes de ton fichier test, que donnerait ce code, éclaté, et où j'effectue les calculs en premier, afin de ne les faire qu'une fois?
PowerQuery:
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Col = List.Distinct(Source[Rubriques]),
    Nb = List.Count(Col),
    Split = List.Split(Source[Informations],Nb),
    From = Table.FromRows(Split,Col)
in
    From
Bonne soirée
OK, je le passerai "à la moulinette" te tien au jus. J'aurai bien aimer joindre le fichier test mais sa taille dépasse la limite autorisée. Je vois au besoin pour le fournir brut sans le résultat des RQ ou via un fichier .txt zippé.
 

oguruma

XLDnaute Occasionnel
ça passe tout juste, je te laisse implémenter ta solution.
 

Pièces jointes

  • Transpose_Blocs_Lignes_Colonnes_V4_Perf_4.5_Brut.zip
    890.3 KB · Affichages: 3

oguruma

XLDnaute Occasionnel
Re-,
Effectivement, le Table.FromRows n'est pas performant...
A retenir
Merci pour ces tests
Effectivement
let
Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
Col = List.Distinct(Source[Rubriques]),
Nb = List.Count(Col),
Split = List.Split(Source[Informations],Nb)
// From = Table.FromRows(Split,Col)
in
Split

ça passe en moins d'une seconde mais le nerf de la guerre se situe ici : From = Table.FromRows(Split,Col)
cependant une nouvelle solution à retenir et à qualifier selon la volumétrie
 

oguruma

XLDnaute Occasionnel
Bonsoir, j'ai poussé les tests de perf jusqu'au maxi
1703278187088.png


Solution de mromain : entre 12s et 14s
la mienne entre 1'25 et 1'30
version macro non testée... je dois la remanier pour travailler en tableau mémoire via un tableau en 2 dim pour ne pas être bloqué par la limite du Transpose (65535 lignes max)
Ce tips va certainement se transformer en ressource Outils (téléchargement) ça pourrait aider ceux qui ont ce type de données.... au cas où des ERP feraient ce genre d'extraction ou autre soft
cet outil embarquera toutes les solutions proposées dans ce post avec les réserves sur les perf.. selon la volumétrie et la solution choisie, ainsi la boucle sera bouclée. :)
Allez Bon Noël :)
 

oguruma

XLDnaute Occasionnel
Bonsoir, c'est sur la table des développements. Voici l'interface d'accueil

1703370709591.png


Transcription des paramètres saisis
1703370759349.png


Et avec ces paramètres le plan d'adressage concernant les noms de champs se calcule automatiquement
1703370820090.png


J'ai conservé en l'état la requête de mromain que j'ai juste rendu paramétrable fonction du contexte des informations renseignées et aussi détecter si le fichier source présente des titres de colonnes

PowerQuery:
let

    //=================================================================================================
    // Auteur : Oguruma2D
    // Forum  : https://excel-downloads.com/
    // Objet  : Adaptation du code de mromain (XLD) afin que la requête soit dynamique
    //=================================================================================================

    // ---------------------------------------------------------------------------
    // Paramètres pour gérer la requête en fonction du contexte des données source
    // ---------------------------------------------------------------------------
    TbSource=getParameters("TB_PARAMS", "PARAM_TB_SOURCE"),
    ColonneTitre=getParameters("TB_PARAMS", "PARAM_COLONNE_TITRE"),
    LabelEtiquette=getParameters("TB_PARAMS", "PARAM_TITRE_ETIQUETTE"),
    LabelValeur=getParameters("TB_PARAMS", "PARAM_TITRE_VALEUR"),
    LabelEtiquetteDefault=getParameters("TB_PARAMS", "PARAM_DEFAULT_COLUMN_1"),
    LabelValeurDefault=getParameters("TB_PARAMS", "PARAM_DEFAULT_COLUMN_2"),
    Etiquette=if ColonneTitre = "OUI" then LabelEtiquette else LabelEtiquetteDefault,
    Valeur=if ColonneTitre = "OUI" then LabelValeur else LabelValeurDefault,

    //=================================================================================================
    // Auteur : mromain
    // Forum  : https://excel-downloads.com/
    // Objet  : Reprise du code en l'état avec remplacement par les paramètres en début de requête
    // Params : TbSource, Etiquette, Valeur
    //=================================================================================================
    Source = Excel.CurrentWorkbook(){[Name=TbSource]}[Content],
    RenameColumns = Table.RenameColumns(Source,{{Etiquette, "Name"}, {Valeur, "Value"}}),
    SplitTable = Table.Split(RenameColumns, List.Count(List.Distinct(RenameColumns[Name]))),
    ConvertToRecord = List.Transform(SplitTable, Record.FromTable),
    ToTable = Table.FromRecords(ConvertToRecord)
in
    ToTable

L'importation des données source se fait aussi par une requête PWQ
PowerQuery:
let

    //=================================================================================================
    // Auteur : Oguruma2D
    // Forum  : https://excel-downloads.com/
    // Objet  : Ceci fait suite au post à propos du dégroupage de lignes en colonnes
    //=================================================================================================

    //-------------------------------------------------------------------------------------------------
    // On récupère les paramètres du fichier source à importr
    // puis si ce fichier source comporte des titres de colonnes
    //-------------------------------------------------------------------------------------------------
    FichierSource=getParameters("TB_PARAMS", "PARAM_FICHIER_SOURCE"),
    ColonneTitre=getParameters("TB_PARAMS", "PARAM_COLONNE_TITRE"),   

    Source = Csv.Document(File.Contents(FichierSource),[Delimiter=";", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    Promote = if ColonneTitre ="OUI" then Table.PromoteHeaders(Source, [PromoteAllScalars=true]) else Source
in
    Promote

A suivre dans le Forum de téléchargement quand l'outil sera totalement opérationnel. En test pour l'instant.
 

oguruma

XLDnaute Occasionnel
Bonjour, utilitaire publié en attente d'approbation. Tout est documenté dans la description de ce dernier. Voilà le Père Noël est passé.. Ho ho ho ho :)
 

Discussions similaires

Statistiques des forums

Discussions
313 286
Messages
2 096 820
Membres
106 755
dernier inscrit
riviere gabriel