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

  • Initiateur de la discussion Initiateur de la discussion eric72
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

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

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

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 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.
 
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
 
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
 
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
 
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 à 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
 
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
 
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

  • Question Question
Réponses
23
Affichages
945
Réponses
16
Affichages
789
Réponses
2
Affichages
364
Retour