Forum Discussion
Solved
Data Layout
Hello
How can I change the layout of my data in the horizontal format?
The current layout of the data is as below.
| unique_key | Header | Value |
| 1015422 | EMD Reason | Select |
| 1015422 | FF1 | M713810 |
| 1015422 | FF10 | NA |
| 1015422 | FF13 | NA |
| 1015422 | FF16 | TEST 1 |
| 1015422 | FF2 | CP214392 |
| 1015422 | FF20 | OPEN |
| 1015422 | FF3 | NA |
| 1015422 | FF33 | DEDW3EE |
| 1015422 | FF45 | LONDON |
| 1015422 | FF46 | 200 |
| 1015422 | FF47 | KMKMHK9 |
| 1015422 | FF48 | Select |
| 1015422 | FF75 | Select |
| 1015422 | Traveller Guid | HUHJI-HUHU-NKN-2342 |
| 1015423 | FF33 | NA |
| 1015423 | FF46 | NA |
| 1015423 | FF47 | NA |
| 1015423 | Traveller Guid | NA |
Required Layout
| Unique Key | Reason | FF1 | FF10 | FF13 | FF16 | FF2 | FF20 | FF3 | FF33 | FF45 | FF46 | FF47 | FF48 | FF75 | Traveller Guid |
| 1015422 | Select | M713810 | NA | NA | TEST 1 | CP214392 | OPEN | NA | DEDW3EE | LONDON | 200 | KMKMHK9 | Select | Select | HUHJI-HUHU-NKN-2342 |
| 1015423 | TEST | NA | NA | NA | NA |
to know how to do this watch my video
3 Replies
pls try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDLDoIwEEV/hbCGpC8ElkaKaG0hAnFBWBDtwoRogo/vt12BGbqZm94z6Z2ZrvMxwhEjxA98LjPvrIfX82EetR719e33wbIjz7GpMsY0wQgyZERtoU8d/sZIw+vGw4DZuqsIZjQlENqgsuIKEEcStX7GswvlHEAWGTmVKivhf8yOSBDclcVGhBSyECmEifuAceRizTR89Tjqydt/7jdjFG1xPISmtqESKiSULU9B583+Nqbz4Gt+vOaDaNvR/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [unique_key = _t, Header = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"unique_key", Int64.Type}, {"Header", type text}, {"Value", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Header]), "Header", "Value") in #"Pivoted Column"to know how to do this watch my video
