Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic transform - columnizing row data

Dear Everyone,   I am not sure if this is feasible, and I am guessing it will be heavy, row by row transformation with a custom M function, but I would like to get some ideas, as how to approach a ...
Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    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"

     

camargos88's avatar
camargos88
Community Champion
6 years ago

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"

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

 

IDProperty NameValue
1CountryBrazil
1Currency USD 
2CurrencyEUR
2ProductRice
2CountryChina

 

-->

 

IDCurrencyProductCountry
1  Brazil
2EURRiceChina
  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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.

     

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    H Anonymous ,

     

    I didn't get your point. ID's can have more than 1 same properties, like ID 1 has 2 countries ?

    If no, the pivot will handle the correct transformation with the values.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes, sorry, you are right - I was confused by the actual values in the data, as a column had very different values and I thought those are values from different fields that should not be in 1 column.

      I did not expect pivoting to also recognize all the columns correctly, but it looks like it does.