Forum Discussion
freddy21
10 months agoFrequent Visitor
Table Transformation
Hello everyone, I have the following problem: I want to display the following table, which currently looks like this: I want this: Id Label1 Label2 Label3 Labe...
- 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"
aTa_Shaikh
9 months agoFrequent Visitor
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]),
Group = Table.Group( Source , "id" , {"x" , each
Table.TransformColumnNames(Table.FromRows( {_[labels]} ) , each Text.Replace(_ , "Column" , "label") ) } ),
toTbls = Table.FromPartitions( "ID" , Table.ToRows(Group) , type number )
in
toTbls
You Can Use This Method in the future