Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all!
How can I combine rows with the same ID into one row by adding the columns at the back of the first row? Please find an explanation in the picture:
Thank you so much for your help!
Solved! Go to Solution.
Hi @bluebock,
This is doable (see below), but I would suggest that you re-think the PQ model. Doing pivoting this way is quite strage, do you really need it?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjFU0lGqTC0GkoZGekBkBBIoSC4CkfnFmSWZZalKsTpQhXn5QMLPEYkASxkrIAwxhhuSmA7SkZqeiDDDCKHOBM0yVIVgE8G2GRrqARFYIYqdCHcbmegZGOsZGeEwygSu0MASbhQuD2J4Al1hLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"w test" = _t, #"w test.date" = _t, #"w .test.method" = _t, #"w test.result" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"w test", type text}, {"w test.date", type text}, {"w .test.method", type text}, {"w test.result", type text}}),
fCombine = (x as table) =>
let
#"Added Index" = Table.AddIndexColumn(x, "Index", 0, 1, Int64.Type),
Pivot = List.Accumulate(List.Skip(Table.ToRecords(#"Added Index")), #"Added Index"{0}, (a, n)=>
let
fields = Record.RemoveFields(n, {"ID", "Index"}),
names = Record.FieldNames(fields),
renamed = List.Transform(names, each _ & "." & Text.From(n[Index])),
out = a & Record.RenameFields(fields, List.Zip({names, renamed}))
in out),
result = Table.FromRecords({Pivot})
in result,
#"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"ID"}, {{"Data", fCombine}})[Data])
in
#"Grouped Rows"
Kind regards,
John
Hi @jbwtp
That's amazing! It worked perfectly fine! Thank you so much, big help!
We are discussing you suggestion.
Thanks again and all the best!
Kind regards
Hi @bluebock,
This is doable (see below), but I would suggest that you re-think the PQ model. Doing pivoting this way is quite strage, do you really need it?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjFU0lGqTC0GkoZGekBkBBIoSC4CkfnFmSWZZalKsTpQhXn5QMLPEYkASxkrIAwxhhuSmA7SkZqeiDDDCKHOBM0yVIVgE8G2GRrqARFYIYqdCHcbmegZGOsZGeEwygSu0MASbhQuD2J4Al1hLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"w test" = _t, #"w test.date" = _t, #"w .test.method" = _t, #"w test.result" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"w test", type text}, {"w test.date", type text}, {"w .test.method", type text}, {"w test.result", type text}}),
fCombine = (x as table) =>
let
#"Added Index" = Table.AddIndexColumn(x, "Index", 0, 1, Int64.Type),
Pivot = List.Accumulate(List.Skip(Table.ToRecords(#"Added Index")), #"Added Index"{0}, (a, n)=>
let
fields = Record.RemoveFields(n, {"ID", "Index"}),
names = Record.FieldNames(fields),
renamed = List.Transform(names, each _ & "." & Text.From(n[Index])),
out = a & Record.RenameFields(fields, List.Zip({names, renamed}))
in out),
result = Table.FromRecords({Pivot})
in result,
#"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"ID"}, {{"Data", fCombine}})[Data])
in
#"Grouped Rows"
Kind regards,
John
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.