cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GeekAlfPro
Advocate I
Advocate I

Pivot Question and group by

Hello,

 

i'm facing an issue and don't know how to resolve it.

i have datas like this : 

 

GeekAlfPro_0-1611313280647.png

and i want to transform like this, where the _6 column have only one row

 

GeekAlfPro_1-1611313314400.png

i tried several things (pivot fill up and so on), but without success

 

any help would be welcome !

1 ACCEPTED SOLUTION
Rocco_sprmnt21
Super User II
Super User II

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY6xDoAgDET/pbMLtKXyLcQBEzed9P9jDwZigstB8njclUKBFgrJY6/3gbsg1GhbCsUpFG5QdE7zoJEz/r/q2WrMQzpNU9cGdFW7ihErTO3UpmrutfozygblwOxHfbAJL00+0BpEa4SbXd1e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, poste = _t, libelle = _t, nombre = _t, taux = _t]),
    #"Raggruppate righe" = Table.Group(Origine, {"id"}, {{"all", each Table.First(_)},{"sal", each Record.FromList(List.Skip(_[taux]), List.Skip(_[poste]))}}),
    #"Tabella all espansa1" = Table.ExpandRecordColumn(#"Raggruppate righe", "all", {"poste", "libelle", "nombre"}, {"poste", "libelle", "nombre"}),
    #"Tabella sal espansa" = Table.ExpandRecordColumn(#"Tabella all espansa1", "sal", {"2391", "2395", "3133", "3137"}, {"2391", "2395", "3133", "3137"})
in
    #"Tabella sal espansa"

View solution in original post

3 REPLIES 3
Rocco_sprmnt21
Super User II
Super User II

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY6xDoAgDET/pbMLtKXyLcQBEzed9P9jDwZigstB8njclUKBFgrJY6/3gbsg1GhbCsUpFG5QdE7zoJEz/r/q2WrMQzpNU9cGdFW7ihErTO3UpmrutfozygblwOxHfbAJL00+0BpEa4SbXd1e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, poste = _t, libelle = _t, nombre = _t, taux = _t]),
    #"Raggruppate righe" = Table.Group(Origine, {"id"}, {{"all", each Table.First(_)},{"sal", each Record.FromList(List.Skip(_[taux]), List.Skip(_[poste]))}}),
    #"Tabella all espansa1" = Table.ExpandRecordColumn(#"Raggruppate righe", "all", {"poste", "libelle", "nombre"}, {"poste", "libelle", "nombre"}),
    #"Tabella sal espansa" = Table.ExpandRecordColumn(#"Tabella all espansa1", "sal", {"2391", "2395", "3133", "3137"}, {"2391", "2395", "3133", "3137"})
in
    #"Tabella sal espansa"

View solution in original post

Hello @Rocco_sprmnt21 

 

thank you very much that's exactly what i tried to do.

have a good day

Rocco_sprmnt21
Super User II
Super User II

help who can help you: upload a table that can be easily copied!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors