Traspose some columns as rows

[GRT]

I have a table like this in power query

               ID                 MES_ID                            UNITS                           GENDER

               61                           202003                  2                                            M                                

               61                           202003                  6                                            W           

And I need to transform my data into     

 ID                 MES_ID                            GENDER_M        GENDER_W

 61                           202003                  2                                   6                              

Thanks

How I can achieve this behabiour with power query editor?

Thanks               

[amitchandak]

Create two new measure like

GENDER_M  =calculate(sum(table[unit]),table[GENDER] ="M")
GENDER_W = calculate(sum(table[unit]),table[GENDER] ="W")

Appreciate your Kudos.

[Greg_Deckler · Accepted solution]

Just pivot your GENDER column and don't aggregate.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjNU0lEyMjAyMDAGMYDYVylWB13cDIjDlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, MES_ID = _t, UNITS = _t, GENDER = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"MES_ID", Int64.Type}, {"UNITS", Int64.Type}, {"GENDER", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[GENDER]), "GENDER", "UNITS")
in
    #"Pivoted Column"