Forum Discussion
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 this one :
| Data |
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 3 |
| 3 |
And I would like to make this operation :
| Data | Index |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
So create an index but the one I made doesn't start again when the value of Data changes. Do you know how to make it please ?
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
4 Replies
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
