Forum Discussion
Column Transformation
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
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
Thank you for answering me.
It works but I've got a problem when I made it. At the beginning, my table has several columns and if I respect your code, at the end I have only two columns in the table.
Do you know how to do to get this table at the end please ?
LOT Index Value 1 Value 2 Value 3 1 1 0,5 2 0,6 1 2 1 3 1 1 3 2 5 2 1 4 6 0,6 2 2 1 0,3 1 1 2 2 0,6 2 5 3 1 1 2 5 3 2 2 0,5 0,2 3 3 2 1 0,1 3 4 1 1 3 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
Thank you for the link, It works well !
