Forum Discussion

Oscar64's avatar
Oscar64
Frequent Visitor
2 years ago
Solved

Group rows with the same identifier and transpose date rows into columns

Hello,

I would like to group the lines with the same "Matricule" and transpose the dates of each line into columns.

Here is the table with the starting information with several lines with the same “Matricule”

Here is the expected result with one line per “Matricule” and several date columns

 

Link to sample file

 

Can you help me get this result with Power Query ?

Thanks in advance to anyone who wants to help me.

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Result:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZbBjtsgEIZfBeW8kgFjJzkiTBy6NrjgnFb7AL20p75/B/DagaRVbDdSIhRlvsz8M/zjj48DwfA6Ht4O5vcP+BRXbls0WPNNjqjn9l2OSvsvpBNWwQ/kgCgmZzgRVlQFxRQfPt82ciiFEy0LWoTzdg6D07Eoi3AMGIIZYzMGfj4gM0jLR2W05wnp3M2hQTa8TcqiEwdv54SyWMHuqtqE8QG0juqwL3XKkGXkSO2kajXXIxJXaeF9s3N0GYtJozGp5mjunHIjBEuEGokaZaXw+fxLjnWEWMFpD8FLSWiiJWhQnh4JCFR16HrrudLSpTKwmEI5p0CrtBmD6SRS+mI5GqW4Gm0606oFE7Wo84lfi4mCHHdjwr3BEbOoQur7yZhGAl1uXZeqgQtCUznKZTaFMbYBLYW9jbKXoKuC7lgdRpV3iSDknFeyHhQlwf8BFEZlGtdZFHLE8LX+9RM+YbrQwO0oNbeKjw4By0LsxdgeibSyuiA4yWg9KCZU5wmd8umfph6hYHZSTnaXzV6u0HpOVJrs5zy9k5jRO9duoV8QhsC1pYD57fmYTw5NG/5q+Ne8bA2n8d8zQ6mXcVtMdfbUqEi4lnkauQj1E86jOf/FWl+Mjtf/wVZfjQ6Zn9MtR0q6CMhbf8m882j1/Sa9jO/KODgm+ZdFvQMR0mD7EKGXLO9BtTDiJFslpPcP76qeCSRx4UktVZLIekiopt4LCX3N/YIdZ98Jy6G1XDfg7qYfxmxBgdmcEz0JWeb6btU+3bR+NZDJHZbNUJXparqa3i+j6cHFP66YmUDiYIV2kE2EaT3eP7MQfKTLtg+7QIH1ByUnY0Ij77yaQj48eewnxatK03WwManQ3ypb27hic4OhR+iiGtkpF/XhTa+gb0Znjab55V8HiTennCGffwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Matricule = _t, #"Actif (O/N)" = _t, Fonction = _t, #"Nom de la campagne" = _t, #"Date de l'EP" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date de l'EP", type date}, {"Matricule", Int64.Type}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Matricule"}, {{"All", each 
        [ a = Table.RemoveColumns(Table.FirstN(_, 1), {"Date de l'EP"}),
          b = List.Accumulate({0..List.Count([#"Date de l'EP"]) -1}, a, (s,c)=> Table.AddColumn(s, "Date de l'EP " & Text.From(c+1), (x)=> [#"Date de l'EP"]{c}, type text))
        ][b], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

     

5 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Result:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZbBjtsgEIZfBeW8kgFjJzkiTBy6NrjgnFb7AL20p75/B/DagaRVbDdSIhRlvsz8M/zjj48DwfA6Ht4O5vcP+BRXbls0WPNNjqjn9l2OSvsvpBNWwQ/kgCgmZzgRVlQFxRQfPt82ciiFEy0LWoTzdg6D07Eoi3AMGIIZYzMGfj4gM0jLR2W05wnp3M2hQTa8TcqiEwdv54SyWMHuqtqE8QG0juqwL3XKkGXkSO2kajXXIxJXaeF9s3N0GYtJozGp5mjunHIjBEuEGokaZaXw+fxLjnWEWMFpD8FLSWiiJWhQnh4JCFR16HrrudLSpTKwmEI5p0CrtBmD6SRS+mI5GqW4Gm0606oFE7Wo84lfi4mCHHdjwr3BEbOoQur7yZhGAl1uXZeqgQtCUznKZTaFMbYBLYW9jbKXoKuC7lgdRpV3iSDknFeyHhQlwf8BFEZlGtdZFHLE8LX+9RM+YbrQwO0oNbeKjw4By0LsxdgeibSyuiA4yWg9KCZU5wmd8umfph6hYHZSTnaXzV6u0HpOVJrs5zy9k5jRO9duoV8QhsC1pYD57fmYTw5NG/5q+Ne8bA2n8d8zQ6mXcVtMdfbUqEi4lnkauQj1E86jOf/FWl+Mjtf/wVZfjQ6Zn9MtR0q6CMhbf8m882j1/Sa9jO/KODgm+ZdFvQMR0mD7EKGXLO9BtTDiJFslpPcP76qeCSRx4UktVZLIekiopt4LCX3N/YIdZ98Jy6G1XDfg7qYfxmxBgdmcEz0JWeb6btU+3bR+NZDJHZbNUJXparqa3i+j6cHFP66YmUDiYIV2kE2EaT3eP7MQfKTLtg+7QIH1ByUnY0Ij77yaQj48eewnxatK03WwManQ3ypb27hic4OhR+iiGtkpF/XhTa+gb0Znjab55V8HiTennCGffwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Matricule = _t, #"Actif (O/N)" = _t, Fonction = _t, #"Nom de la campagne" = _t, #"Date de l'EP" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date de l'EP", type date}, {"Matricule", Int64.Type}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Matricule"}, {{"All", each 
        [ a = Table.RemoveColumns(Table.FirstN(_, 1), {"Date de l'EP"}),
          b = List.Accumulate({0..List.Count([#"Date de l'EP"]) -1}, a, (s,c)=> Table.AddColumn(s, "Date de l'EP " & Text.From(c+1), (x)=> [#"Date de l'EP"]{c}, type text))
        ][b], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

     

  • Oscar64's avatar
    Oscar64
    Frequent Visitor
    2 years ago

    Many thanks for this quick and perfect solution. 😀
    I just tested it on my data, and it works perfectly. 👍