Forum Discussion

VJ_Paris's avatar
VJ_Paris
New Member
3 years ago

Transforming rows into columns

Hello   I would like to transform a table with rows into columns   As you can see I need to get the field with the date on each rows. I don't need the column Type Some time there is a Nom d'usa...
Need Help
CNENFRNL's avatar
CNENFRNL
Community Champion
3 years ago

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBLCoMwEAbgq0jWFmYmEuNSsAUXrS7cWRdibRHUFKuFHslzeLH6WAg+Ct1l5if5ZhKGTCX1gYjpjEV6yILPM+3PbnlXY31RhXZLtVNcZHk+JPbcvjYAZDav+DEHftW1pSr62sexcY7rKkua8a6BgFzS9EJTdG2ltESVdRXXQwoGt6wpLdX7n6ECL/CWPK14SQiElrnjS5IkTBI7hB24x6XBtw3i+MuQYG0bju2sftFYEQIAEJHvEAJEvyX0a0Rf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "Type")),

    #"Grouped Table" = Table.Group(#"Filtered Rows", "Column1", {"Grp Mois", each Table.Group(Table.Skip(_), "Column1", {"Grp Nom", each _}, 0, (x,y) => Byte.From(y="Nom de Famille"))}, 0, (x,y) => Byte.From(Text.PositionOfAny(y, {"0".."9"}, Occurrence.First) <> -1)),
    #"Expanded Grp Mois" = Table.ExpandTableColumn(#"Grouped Table", "Grp Mois", {"Grp Nom"}),
    #"Transformed Column" = Table.TransformColumns(#"Expanded Grp Mois", {"Grp Nom", each Table.PromoteHeaders(Table.Transpose(_))}),
    #"Expanded Grp Nom" = Table.ExpandTableColumn(#"Transformed Column", "Grp Nom", {"Nom de Famille", "Nom d'usage", "Prénom", "Matricule", "Numéro contrat"})
in
    #"Expanded Grp Nom"