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.
Hello Community,
I need to calculate SORT BY Column grouped by each LOT_NUMBER Column and sorted in an ascending STAGE SEQUENCE Column.
SORT BY Column should be like this:
LOT_NUMBER | STAGE SEQUENCE | SORT BY |
0019 | 6 | 1 |
0019 | 7 | 2 |
0019 | 4 | 3 |
0019 | 2 | 4 |
0019 | 5 | 5 |
0019 | 3 | 6 |
0019 | 1 | 7 |
0020 | 5 | 1 |
0020 | 6 | 2 |
0020 | 3 | 3 |
0020 | 2 | 4 |
0020 | 4 | 5 |
0020 | 1 | 6 |
Thanks in advance for your support.
Solved! Go to Solution.
Hi @ss89 ,
Here I suggest you to add an index column grouped by LOT_NUMBER column in Power Query Editor.
Copy the code as below and paste it into Advanced Editor in Power Query Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcyxCQAgDADBXVJbxKgRZwnZfw1FRL48eD5CVOuSIi5ZPibRCSMG0Yj6YIrswolGGNGJc8sN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LOT_NUMBER = _t, #"STAGE SEQUENCE" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LOT_NUMBER", Int64.Type}, {"STAGE SEQUENCE", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"LOT_NUMBER"}, {{"Count", each _, type table [LOT_NUMBER=nullable number, STAGE SEQUENCE=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"STAGE SEQUENCE", "Index"}, {"Custom.STAGE SEQUENCE", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.STAGE SEQUENCE", "STAGE SEQUENCE"}, {"Custom.Index", "Index"}})
in
#"Renamed Columns"
New Table:
For reference:Create Row Number for Each Group in Power BI using Power Query
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ss89 ,
Here I suggest you to add an index column grouped by LOT_NUMBER column in Power Query Editor.
Copy the code as below and paste it into Advanced Editor in Power Query Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcyxCQAgDADBXVJbxKgRZwnZfw1FRL48eD5CVOuSIi5ZPibRCSMG0Yj6YIrswolGGNGJc8sN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LOT_NUMBER = _t, #"STAGE SEQUENCE" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LOT_NUMBER", Int64.Type}, {"STAGE SEQUENCE", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"LOT_NUMBER"}, {{"Count", each _, type table [LOT_NUMBER=nullable number, STAGE SEQUENCE=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"STAGE SEQUENCE", "Index"}, {"Custom.STAGE SEQUENCE", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.STAGE SEQUENCE", "STAGE SEQUENCE"}, {"Custom.Index", "Index"}})
in
#"Renamed Columns"
New Table:
For reference:Create Row Number for Each Group in Power BI using Power Query
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |