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

eric72 · 18 Mars 2024

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

djidji59430 · 18 Mars 2024

Bonjour à tous,

une idée, peut etre pas geniale, mais qui fonctionne sur ton tableau

Cousinhub · 18 Mars 2024

Bonsoir,
Une solution Power Query
Bonne soirée

eric72 · 19 Mars 2024

djidji59430 à dit:
Bonjour à tous,

une idée, peut etre pas geniale, mais qui fonctionne sur ton tableau

Bonjour,
Merci pour votre réponse, mais cela ne correspond pas à ce que je souhaite. Par contre Cousinhub a trouvé exactement ce qui me correspond.
Merci beaucoup et bonne journée

eric72 · 19 Mars 2024

Cousinhub à dit:
Bonsoir,
Une solution Power Query
Bonne soirée

Bonjour,
Merci beaucoup pour la réponse, c'est exactement ce que je souhaitais mis n'y parvenais pas.
Grâce à vous je suis sauvé 😉
Bonne journée

eric72 · 19 Mars 2024

Cousinhub à dit:
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

alexga78 · 19 Mars 2024

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 · 19 Mars 2024

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

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 · 19 Mars 2024

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

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

eric72 · 19 Mars 2024

Cousinhub à dit:
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

Bonjour,
Merci pour ta réponse, en effet la solution d'Alex parait plus simple, je prends note pour les fautes d'orthographe Pffff suis nul!!!
Merci pour tout à vous deux et bonne soirée 🙏
Eric

alexga78 · 19 Mars 2024

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 · 20 Mars 2024

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

Bonjour,
C'est juste génial, celle là je la garde au chaud.
Merci beaucoup et belle journée

JFL_XLD · 20 Mars 2024

Bonjour à tous !
Très largement après la bataille, je vous livre une approche différente qui :

respecte l'ordre des lignes
respecte les champs attendus
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 · 20 Mars 2024

JFL_Excel à dit:

Bonjour à tous !
Très largement après la bataille, je vous livre une approche différente qui :

respecte l'ordre des lignes
respecte les champs attendus
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 · 20 Mars 2024

Bonjour à tous de nouveau !

eric72 à dit:
........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

Effectuez une recherche sur Excel Downloads...

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

eric72

XLDnaute Accro

Pièces jointes

alexga78

djidji59430

XLDnaute Barbatruc

Pièces jointes

Cousinhub

XLDnaute Barbatruc

Pièces jointes

eric72

XLDnaute Accro

eric72

XLDnaute Accro

eric72

XLDnaute Accro

Pièces jointes

alexga78

XLDnaute Occasionnel

eric72

XLDnaute Accro

Cousinhub

XLDnaute Barbatruc

eric72

XLDnaute Accro

alexga78

XLDnaute Occasionnel

eric72

XLDnaute Accro

JFL_XLD

XLDnaute Junior

eric72

XLDnaute Accro

JFL_XLD

XLDnaute Junior

Discussions similaires

Nous accordons de l'importance à votre vie privée