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

[Oscar64]

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.

[Oscar64]

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

[dufoq3]

You're welcome.

[dufoq3 · Accepted solution]

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]

Sorry for the link.

I think it must be good.

[dufoq3]

Hi, make the link public please.