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: 7
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
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

E
  • Résolu(e)
Microsoft 365 Grand Livre données exploitables
2
Réponses
28
Affichages
542
eric72
E
O
  • Question
Power Query Convertir un tableau de colonnes en lignes avec plusieurs variables (power query)
Réponses
8
Affichages
370
oobiz
O
M
  • Question
XL 2010 Récupération de données et les transformer avec Power Query ?
Réponses
3
Affichages
256
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
458
Merlin258413
M
T
  • Question
XL 2016 Difficultés pour extraire des données d'une BDD et autres petits détails !!!
Réponses
13
Affichages
838
ChTi160
ChTi160
M
  • Résolu(e)
Microsoft 365 VBA pour transformer une plage en tableau + Générer un tableau de synthèse
Réponses
10
Affichages
338
Merlin258413
M
P
  • Question
XL 2021 Réaliser un échéancier à partir d'un TCD et de données en colonne
Réponses
2
Affichages
535
JHA
J
A
  • Question
Power Query Variabiliser automatiquement une source de données PQ selon l’utilisateur
Réponses
3
Affichages
176
chris
chris
T
  • Question
XL 2016 Transfert de données d'un tableau à un autre
2
Réponses
16
Affichages
894
merinos
merinos
P
  • Résolu(e)
XL pour MAC Extraire les données d'un tableau en fonction du choix de l'année
2
Réponses
21
Affichages
1 K
pilou010
P

Membres actuellement en ligne

15 membres et 137 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
312 211
Messages
2 086 292
Membres
103 171
dernier inscrit
clemm