Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Can you help me get this result with Power Query ?
Thanks in advance to anyone who wants to help me.
Solved! Go to 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
Many thanks for this quick and perfect solution. 😀
I just tested it on my data, and it works perfectly. 👍
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
Sorry for the link.
I think it must be good.