Forum Discussion

JonathanJohns's avatar
JonathanJohns
Helper III
8 years ago
Solved

Column Transformation

Good afternoon,   I would like to make a specifical column transformation but I don't find the way to make it. I tried with Index Column but it is not exactly what I want.   I have a table like t...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi JonathanJohns,

     

    In this case you should follow the video that ImkeF posted on the previous link

     

     

    So in this case your M code would be the following:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5BCgAgCAT/4jkijfxM+P9vlG1GHswYdmTnJKZCrY79yvkpWQH16WcH8QSSQfQ6wXCjX5s/ps93FolM5HVpVRJFS04u+pkt", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [LOT = _t, #"Value 1" = _t, #"Value 2" = _t, #"Value 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOT", Int64.Type}, {"Value 1", type number}, {"Value 2", type number}, {"Value 3", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"LOT"}, {{"Count", each Table.AddIndexColumn (_, "Index",1,1), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Value 1", "Value 2", "Value 3", "Index"}, {"Count.Value 1", "Count.Value 2", "Count.Value 3", "Count.Index"})
in
    #"Expanded Count"

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
8 years ago

Hi JonathanJohns,

 

 

Following the solution provided by ImkeF (a great datanaut) in this post you can achieve the desired result, please read the post carefully to understand the way things are setup.

 

Below is the M code I used for your model data to achieve the result:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1cFGGiGRxtjIWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Data"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index"}, {"Custom.Index"})
in
    #"Expanded Custom1"

Regards,

MFelix