Column Transformation

[JonathanJohns]

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 ?

[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

[JonathanJohns]

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

[MFelix · Accepted solution]

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

[JonathanJohns]

Thank you for the link, It works well !