Forum Discussion
Tabular Format to List Field Value
Hi Team,
I have table data in below format
| FieldName | Value |
| FirstName | Atul |
| LastName | Kumar |
| Age | 28 |
| Designation | Programmer |
| FirstName | Tony |
| LastName | M |
| Age | 32 |
| Designation | Team Lead |
| FirstName | Ken |
| LastName | T |
| Age | 40 |
| Designation | SME, PM |
And I want to conver this to proper tabular format as below :
| FirstName | LastName | Age | Designation |
| Atul | Kumar | 28 | Programmer |
| Tony | M | 32 | Team Lead |
| Ken | T | 40 | SME, PM |
Some field value may have Comma in between.
Hi Pat,
Thanks for your time and reply. Appreciate your time. I got the desinred result using simple Table.FromRows function.
= Table.FromRows( List.Split(MyData[Field_Value], 4) )
= Table.RenameColumns(Source,{{"Column1", "FirstName"}, {"Column2", "LastName"}, {"Column3", "Age"}, {"Column4", "Designation"}})
MyData is the table name and there were 4 repated colum values in Vertical table.
2 Replies
Please see the example M code below for one way to transform your example data. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsssKi7xS8xNVdJRciwpzVGK1YlW8kmEi3mX5iYWgQUd00F8IwswxyW1ODM9L7EkMz8PKBhQlJ9elJibmwpRiWxmSH5eJbqZvkjmGRthMS8kNTFXwSc1MQXDOO/UPHTTQpBMMzHAYlqwr6uOQgDQ0lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FieldName = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"FieldName", type text}, {"Value", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "ItemNumber", each Number.RoundUp([Index]/4,0)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[FieldName]), "FieldName", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"ItemNumber"}) in #"Removed Columns1"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi Pat,
Thanks for your time and reply. Appreciate your time. I got the desinred result using simple Table.FromRows function.
= Table.FromRows( List.Split(MyData[Field_Value], 4) )
= Table.RenameColumns(Source,{{"Column1", "FirstName"}, {"Column2", "LastName"}, {"Column3", "Age"}, {"Column4", "Designation"}})
MyData is the table name and there were 4 repated colum values in Vertical table.
