Forum Discussion
Table Transformation
- 10 months ago
Add an Index column starting at 1 and incrementing by 1.
Replace the values in the Index column, adding the 'Label' prefix.
Pivot the transformed Index column. Use the 'labels' column as the Values column with no aggregation.
Complete example code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQxNzE2tTQzVdJRMjAwMFJwSi1KLCnNS1eK1cGQNVHwz8vJzEtV8E0syk4tycSuykzBozQpuCC/BFPSSM9QIbgEaEFqemaqUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, labels = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"labels", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Index], each "Label"&Number.ToText([Index]),Replacer.ReplaceValue,{"Index"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Index]), "Index", "labels") in #"Pivoted Column" - 10 months ago
With more than one ID, you can
- Group by ID
- Create a list of Field Names dependent on the number of rows in the grouped table.
- Create a list of records from the label values and the field names
- Expand the records into new rows
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQxNzE2tTQzVdJRMjAwMFJwSi1KLCnNS1eK1cGQNVHwz8vJzEtV8E0syk4tycSuykzBozQpuCC/BFPSSM9QIbgEaEFqemYqRNrQ0MjYxNTMHKLXUiEjPysRU8bQwFAhLb+ouCQxJ0cpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, labels = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"labels", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, { {"Row", (t)=> [a=List.Repeat({"Label"}, Table.RowCount(t)), b=List.Transform(List.Numbers(1, List.Count(a)), each Text.From(_)), c=List.Zip({a,b}), d=List.Transform(c, each Text.Combine(_, " ")), e=Record.FromList(t[labels],d)][e]}}), #"Field Names" = List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[Row], each Record.FieldNames(_)))), #"Expanded Row" = Table.ExpandRecordColumn(#"Grouped Rows", "Row", #"Field Names") in #"Expanded Row"
Hi freddy21 ,
Thank you for reaching out to the Microsoft Community Forum.
Hi jgeddes , Thank you for the prompt response.
Hi freddy21 , Could you please try the proposed solution shared by jgeddes ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.
Regards,
Dinesh
Hi freddy21 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh