Microsoft 365 Synthèse Données en dépivotant

E

eric72

XLDnaute Accro
Bonjour à tous,
J'ai un tableau qui est rempli par un userform et j'aimerais transformer ce tableau en dépivotant, j'ai regardé des exemples POWER QUERY mais je n'y parviens pas à moins qu'il y ait une autre solution par formule, la raison de cette transformation est que, ensuite je veux faire une recherchex par rapport à la ville et à la "tranche" du nombre de salariés pour obtenir le prix.
Auriez vous une petite idée géniale?
Merci beaucoup pour le coup de main une nouvelle fois ;)
Eric
 

Pièces jointes

  • TEST.xlsm
    30 KB · Affichages: 8
Solution
A
Bonjour eric72, le forum,

Pas persuadé d'avoir compris la demande mais un essai avec power Query, peut-être plus facilement adaptable aux données réelles.

PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="TbTarifSite"]}[Content],
    ColName = List.Distinct(List.Transform(Table.ColumnNames(Source), each if Text.Contains(_,")") then Text.BeforeDelimiter(_, ")") & ")" else _)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {ColName{0}}, "Attribute", "Value"),
    #"Added Siège" = Table.AddColumn(#"Unpivoted Other Columns", "Custom",  each Text.End([Attribute], 1)),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Added Siège", {ColName{0}, "Custom"}, {{"Count", each Table.SelectColumns(Table.FromColumns(List.Transform(List.InsertRange(_[Value],0...
Trier par date Trier par votes
E

eric72

XLDnaute Accro
Cousinhub à dit:
Bonsoir,
Une solution Power Query
Bonne soirée
Cliquez pour agrandir...
Bonjour Cousinhub,
Comme j'ai plusieurs tableaux à traiter de cette manière j'ai essayé de dupliquer sur un autre avec un nombre de critères différent, et là ça bloque le message est:
5 arguments ont été passés à une fonction qui en attendit entre 3 et 4
J'ai bien exploré ma requête et je ne vois pas ou cela bloque, il y a surement une subtilité que je n'ai pas capté.
Pourriez-vous m'indiquer l'endroit ou j'ai fait l'erreur?
Merci beaucoup et désolé pour le dérangement
 

Pièces jointes

  • PQ_Lignes en colonnes.xlsm
    39.8 KB · Affichages: 3
Vote positif 0 Vote négatif
A

alexga78

XLDnaute Occasionnel
Bonjour eric72, le forum,

Pas persuadé d'avoir compris la demande mais un essai avec power Query, peut-être plus facilement adaptable aux données réelles.

PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="TbTarifSite"]}[Content],
    ColName = List.Distinct(List.Transform(Table.ColumnNames(Source), each if Text.Contains(_,")") then Text.BeforeDelimiter(_, ")") & ")" else _)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {ColName{0}}, "Attribute", "Value"),
    #"Added Siège" = Table.AddColumn(#"Unpivoted Other Columns", "Custom",  each Text.End([Attribute], 1)),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Added Siège", {ColName{0}, "Custom"}, {{"Count", each Table.SelectColumns(Table.FromColumns(List.Transform(List.InsertRange(_[Value],0,  { Record.Field(_{0}, ColName{0})} ), each {_}), ColName), ColName)}})[Count])
in
    #"Grouped Rows"

Bonne soirée
 
Vote positif 0 Vote négatif
Solution
E

eric72

XLDnaute Accro
alexga78 à dit:
Bonjour eric72, le forum,

Pas persuadé d'avoir compris la demande mais un essai avec power Query, peut-être plus facilement adaptable aux données réelles.

PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="TbTarifSite"]}[Content],
    ColName = List.Distinct(List.Transform(Table.ColumnNames(Source), each if Text.Contains(_,")") then Text.BeforeDelimiter(_, ")") & ")" else _)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {ColName{0}}, "Attribute", "Value"),
    #"Added Siège" = Table.AddColumn(#"Unpivoted Other Columns", "Custom",  each Text.End([Attribute], 1)),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Added Siège", {ColName{0}, "Custom"}, {{"Count", each Table.SelectColumns(Table.FromColumns(List.Transform(List.InsertRange(_[Value],0,  { Record.Field(_{0}, ColName{0})} ), each {_}), ColName), ColName)}})[Count])
in
    #"Grouped Rows"

Bonne soirée
Cliquez pour agrandir...
Bonjour Alexga78,
Merci beaucoup pour la réponse, cela fonctionne nickel, par contre la formule POWER QUERY suivante:
VB: 
let
    Source = Excel.CurrentWorkbook(){[Name="TbTarifSite"]}[Content],
    UnPivot = Table.UnpivotOtherColumns(Source, {"VILLE"}, "Attribut", "Valeur"),
    ExtactTexte = Table.TransformColumns(UnPivot, {{"Attribut", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    ModType = Table.TransformColumnTypes(ExtactTexte,{{"Valeur", type text}}),
    GroupBy = Table.Group(ModType, {"VILLE", "Attribut"}, {{"Nombre", each Text.Combine([Valeur],"\")}}),
    Pivot = Table.Pivot(GroupBy, List.Distinct(GroupBy[Attribut]), "Attribut", "Nombre"),
    Tbl = Table.AddColumn(Pivot, "T_Recap", each
        let
            SS=Text.Split([#"Externalisee Nb Salariés (Siège)"],"\"),
            BS=Text.Split([#"Externalisee Prix Bulletin (Siège)"],"\"),
            AS=Text.Split([#"Externalisee Accompagnement quotidien RH (Siège)"],"\"),
            PS=Text.Split([#"Externalisee Prix global (Siège)"],"\"),
            SC=Text.Split([#"Externalisee Nb Salariés (Cabinet)"],"\"),
            BC=Text.Split([#"Externalisee Prix Bulletin (Cabinet)"],"\"),
            AC=Text.Split([#"Externalisee Accompagnement quotidien RH (Cabinet)"],"\"),
            PC=Text.Split([#"Externalisee Prix global (Cabinet)"],"\"),
           Rcp=Table.FromColumns({SS,BS,AS,PS,SC,BC,AC,PC},{"Externalisee Nb Salariés (Siège)","Externalisee Prix Bulletin (Siège)","Externalisee Accompagnement quotidien RH (Siège)","Externalisee Prix global (Siège)","Externalisee Nb Salariés (Cabinet)","Externalisee Prix Bulletin (Cabinet)","Externalisee Accompagnement quotidien RH (Cabinet)","Externalisee Prix global (Cabinet)"})
        in
           Rcp),
    SupprCol = Table.SelectColumns(Tbl,{"VILLE", "T_Recap"}),
    Expand = Table.ExpandTableColumn(SupprCol, "T_Recap", {"Externalisee Nb Salariés (Siege)", "Externalisee Prix Bulletin (Siège)","Externalisee Accompagnement quotidien RH (Siège)","Externalisee Prix global (Siège)", "Externalisee Nb Salariés (Cabinet)", "Externalisee Prix Bulletin (Cabinet)","Externalisee Accompagnement quotidien RH (Cabinet)", "Externalisee Prix global (Cabinet)"}),
    ModType1 = Table.TransformColumnTypes(Expand,{{"Externalisee Nb Salariés (Siège)", Int64.Type}, {"Externalisee Prix Bulletin (Siège)", Int64.Type}, {"Externalisee Accompagnement quotidien RH (Siège)", Int64.Type}, {"Externalisee Prix global (Siège)", Int64.Type}, {"Externalisee Nb Salariés (Cabinet)", Int64.Type}, {"Externalisee Prix Bulletin (Cabinet)", Int64.Type}, {"Externalisee Accompagnement quotidien RH (Cabinet)", Int64.Type}, {"Externalisee Prix global (Cabinet)", Int64.Type}})

in
    ModType1
devait donner le même résultat mais je n'ai pas compris pourquoi elle me renvoyait une erreur sur le nombre d'arguments (c'est juste pour ma progression en POWER QUERY que j'aimerais savoir)
Merci beaucoup encore une fois.
 
Vote positif 0 Vote négatif
Cousinhub

Cousinhub

XLDnaute Barbatruc
Inactif
eric72 à dit:
Bonjour Cousinhub,
Comme j'ai plusieurs tableaux à traiter de cette manière j'ai essayé de dupliquer sur un autre avec un nombre de critères différent, et là ça bloque le message est:
5 arguments ont été passés à une fonction qui en attendit entre 3 et 4
J'ai bien exploré ma requête et je ne vois pas ou cela bloque, il y a surement une subtilité que je n'ai pas capté.
Pourriez-vous m'indiquer l'endroit ou j'ai fait l'erreur?
Merci beaucoup et désolé pour le dérangement
Cliquez pour agrandir...
Bonjour,
L'erreur provient des fautes d'orthographe dans les noms des colonnes.
Mais la solution d'alex est bien plus "simple"
Bonne fin d'apm
 
Vote positif 0 Vote négatif
A

alexga78

XLDnaute Occasionnel
Bonsoir Eric72, Cousinhub, le forum,

Si la configuration des données source peut évoluer, l'idéal est de rendre la requête dynamique.

PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="TbTarifSite"]}[Content],
    UnPivot = Table.UnpivotOtherColumns(Source, {Table.ColumnNames(Source){0}}, "Attribut", "Valeur"),
    ExtactTexte = Table.TransformColumns(UnPivot, {{"Attribut", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    ModType = Table.TransformColumnTypes(ExtactTexte,{{"Valeur", type text}}),
    GroupBy = Table.Group(ModType, {Table.ColumnNames(Source){0}, "Attribut"}, {{"Nombre", each Text.Combine([Valeur],"\")}}),
    Pivot = Table.Pivot(GroupBy, List.Distinct(GroupBy[Attribut]), "Attribut", "Nombre"),
    ColName = Table.ColumnNames(Pivot),
    Result = Table.FillDown(Table.Combine(Table.AddColumn(Pivot, "T_Recap",  each Table.FromColumns(List.Accumulate(ColName,{},(s,c)=> s & {Text.Split(Record.Field(_,c) ,"\")}), ColName))[T_Recap]), {ColName{0}})
in
    Result

Bonne soirée à tous
 
Vote positif 0 Vote négatif
E

eric72

XLDnaute Accro
alexga78 à dit:
Bonsoir Eric72, Cousinhub, le forum,

Si la configuration des données source peut évoluer, l'idéal est de rendre la requête dynamique.

PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="TbTarifSite"]}[Content],
    UnPivot = Table.UnpivotOtherColumns(Source, {Table.ColumnNames(Source){0}}, "Attribut", "Valeur"),
    ExtactTexte = Table.TransformColumns(UnPivot, {{"Attribut", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    ModType = Table.TransformColumnTypes(ExtactTexte,{{"Valeur", type text}}),
    GroupBy = Table.Group(ModType, {Table.ColumnNames(Source){0}, "Attribut"}, {{"Nombre", each Text.Combine([Valeur],"\")}}),
    Pivot = Table.Pivot(GroupBy, List.Distinct(GroupBy[Attribut]), "Attribut", "Nombre"),
    ColName = Table.ColumnNames(Pivot),
    Result = Table.FillDown(Table.Combine(Table.AddColumn(Pivot, "T_Recap",  each Table.FromColumns(List.Accumulate(ColName,{},(s,c)=> s & {Text.Split(Record.Field(_,c) ,"\")}), ColName))[T_Recap]), {ColName{0}})
in
    Result

Bonne soirée à tous
Cliquez pour agrandir...
Bonjour,
C'est juste génial, celle là je la garde au chaud.
Merci beaucoup et belle journée
 
Vote positif 0 Vote négatif
J

JFL_XLD

XLDnaute Junior
Bonjour à tous !
Très largement après la bataille, je vous livre une approche différente qui :
  1. respecte l'ordre des lignes
  2. respecte les champs attendus
  3. gère les blocs incomplets.
PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    ToCol = List.Alternate(Table.ToColumns(Table.RemoveColumns(Source,{"VILLES"})),1,3,2),
    ListSplit = List.Transform(List.Split(ToCol,3), each {Source[VILLES]}& _),
    Transf_Combine = Table.Combine(List.Transform(ListSplit, each Table.FromColumns(_,List.RemoveRange(List.Distinct(List.Transform(Table.ColumnNames(Source), each Text.Trim(Text.Remove(_, {"0".."9"})))),3,1)))),
    SuppLignes = Table.SelectRows(Transf_Combine, each not List.IsEmpty(List.RemoveMatchingItems(List.Skip(Record.FieldValues(_)), {"", null})))
in
    SuppLignes
 
Vote positif 0 Vote négatif
E

eric72

XLDnaute Accro
JFL_Excel à dit:
Bonjour à tous !
Très largement après la bataille, je vous livre une approche différente qui :
  1. respecte l'ordre des lignes
  2. respecte les champs attendus
  3. gère les blocs incomplets.
PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    ToCol = List.Alternate(Table.ToColumns(Table.RemoveColumns(Source,{"VILLES"})),1,3,2),
    ListSplit = List.Transform(List.Split(ToCol,3), each {Source[VILLES]}& _),
    Transf_Combine = Table.Combine(List.Transform(ListSplit, each Table.FromColumns(_,List.RemoveRange(List.Distinct(List.Transform(Table.ColumnNames(Source), each Text.Trim(Text.Remove(_, {"0".."9"})))),3,1)))),
    SuppLignes = Table.SelectRows(Transf_Combine, each not List.IsEmpty(List.RemoveMatchingItems(List.Skip(Record.FieldValues(_)), {"", null})))
in
    SuppLignes
Cliquez pour agrandir...
Bonjour,
Même avec du retard c'est toujours intéressant d'avoir une autre approche, cela supprime t il les lignes quand toute la ligne est à 0 sauf le nom de la ville?
Je pense que cette requête est liée au nombre de colonnes aussi.
Merci beaucoup
 
Vote positif 0 Vote négatif
J

JFL_XLD

XLDnaute Junior
Bonjour à tous de nouveau !
eric72 à dit:
........cela supprime t il les lignes quand toute la ligne est à 0 sauf le nom de la ville?
Cliquez pour agrandir...
En modifiant l'argument de la fonction List.RemoveMatchingItems :
PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    ToCol = List.Alternate(Table.ToColumns(Table.RemoveColumns(Source,{"VILLES"})),1,3,2),
    ListSplit = List.Transform(List.Split(ToCol,3), each {Source[VILLES]}& _),
    Transf_Combine = Table.Combine(List.Transform(ListSplit, each Table.FromColumns(_,List.RemoveRange(List.Distinct(List.Transform(Table.ColumnNames(Source), each Text.Trim(Text.Remove(_, {"0".."9"})))),3,1)))),
    SuppLignes = Table.SelectRows(Transf_Combine, each not List.IsEmpty(List.RemoveMatchingItems(List.Skip(Record.FieldValues(_)), {"",0,null})))
in
    SuppLignes
 
Vote positif 0 Vote négatif

Discussions similaires

pilou010
  • Résolu(e)
XL pour MAC Fusion de 2 feuilles dont l'une est issue de Power Query
Réponses
5
Affichages
389
chris
chris
M
  • Question
XL 2019 Transposer des données d'une feuille vers une autre sous forme de synthèse
Réponses
8
Affichages
172
mromain
mromain
E
  • Résolu(e)
Microsoft 365 Grand Livre données exploitables
2
Réponses
28
Affichages
1 K
eric72
E
E
  • Résolu(e)
Microsoft 365 POWER QUERY Pivoter la colonne
Réponses
14
Affichages
367
eric72
E
O
  • Question
Power Query Convertir un tableau de colonnes en lignes avec plusieurs variables (power query)
Réponses
8
Affichages
694
oobiz
O
D
  • Question
Microsoft 365 Remplacer des données manquantes et en ajouter d'autres
Réponses
3
Affichages
325
DFER
D
M
  • Question
XL 2010 Récupération de données et les transformer avec Power Query ?
Réponses
3
Affichages
480
DJunqueira
D
M
  • Résolu(e)
Power Query Convertir un fichier PdF en Excel et restitution d'un état de synthèse
Réponses
10
Affichages
825
Merlin258413
M
P
  • Résolu(e)
XL 2021 Besoin d'aide pour copier/coller données de plusieurs classeurs
2
Réponses
20
Affichages
599
Pac41240
P
W
  • Question
XL 2016 Signaler des bilocations du fait d'horaires identiques ou en chevauchement [selon le contenu de la colonne L du fichier]
2 3
Réponses
34
Affichages
794
Webperegrino
W

Membres actuellement en ligne

9 membres et 83 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
314 708
Messages
2 112 090
Membres
111 416
dernier inscrit
philipperoy83