Forum Discussion
Dynamic transform - columnizing row data
Hi Anonymous ,
Try this m code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJOLElNzy+qBDLdixIz84qVYnWgMqVFRal5ySCZ0GAXhHB+aV5JUaVCcEZmQW5qXomCf1FmemYeUMapKLEqMweuMNTfF0j6hoAFjFCtck4sKkaIY1pkhNciz7yC0hKwOmNUY93y81MQ4ghjXUODlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Category Name" = _t, Values = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Category Name", type text}, {"Values", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Category Name"]), "Category Name", "Values")
in
#"Pivoted Column"
Thanks camargos88 this is the right direction, but the harder part is to make this dynamic, meaning each ID can have a different number of Properties, ordered in a changing way, so the columns should pick them up in the right order. Right now, a single pivot step will lead to the correct structure, but the columns will have mixed values, from other columns.
I will provide an example of this below.
| ID | Property Name | Value |
| 1 | Country | Brazil |
| 1 | Currency USD | |
| 2 | Currency | EUR |
| 2 | Product | Rice |
| 2 | Country | China |
-->
| ID | Currency | Product | Country |
| 1 | Brazil | ||
| 2 | EUR | Rice | China |
Hi Anonymous ,
I pivot the "Property" column and returns the following result.
So how do you manage "Currency USD"? You could share your thoughts here.