Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you for the link, It works well !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |