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

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

eric72

XLDnaute Accro
Bonsoir,
Une solution Power Query
Bonne soirée
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

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
 

eric72

XLDnaute Accro
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
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.
 

Cousinhub

XLDnaute Barbatruc
Inactif
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
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
 

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
 

eric72

XLDnaute Accro
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
Bonjour,
C'est juste génial, celle là je la garde au chaud.
Merci beaucoup et belle journée
 

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
 

eric72

XLDnaute Accro
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
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
 

JFL_XLD

XLDnaute Junior
Bonjour à tous de nouveau !
........cela supprime t il les lignes quand toute la ligne est à 0 sauf le nom de la ville?
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
 

Discussions similaires

Réponses
14
Affichages
206

Statistiques des forums

Discussions
313 309
Messages
2 097 032
Membres
106 812
dernier inscrit
Excellou74