Forum Discussion
What about assigning unique values with multiple criteria in power BI as like , for instance.
- 4 years ago
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMDA1tzQ3UNJRsjAws7AwMTRXitUhTcICRcIQIWGJQ8LIgDijjBA6DEmWMMIlYYxLwkQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, #"Delivery no." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Purchase Order", Int64.Type}, {"Delivery no.", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"Purchase Order"}, {{"All", each _}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let DummyTbl = [All], output = Table.AddColumn(DummyTbl,"Result",each List.PositionOf(List.Distinct(DummyTbl[#"Delivery no."]),[#"Delivery no."])+1) in output), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Delivery no.", "Index", "Result"}, {"Delivery no.", "Index", "Result"}), #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Index", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Purchase Order", Int64.Type}, {"Delivery no.", Int64.Type}, {"Result", Int64.Type}}) in #"Changed Type1" - Anonymous4 years ago
Thank you vijay, your solution is successfully worked.
Just wanted to understand the problem.
For a string "ca", it is assigning the value 1 which is nothing but the value of second letter.
If you notice the data posted by you, it is always the second letter value and first letter doesn't matter.
Is it true? For example if I have "cd", it will be 4.
Yes, if cd it has 4.
Letter is only taking as example, 1st letter is considered in 1st column and 2nd letter is considered in 2nd column.
Below is the another example for better understand.
| Column 1 | Column 2 | Column 3 |
| Purchase Order | Delivery no. | Result |
| 400057970 | 80688417 | 1 |
| 400057970 | 80688417 | 1 |
| 400057970 | 80688418 | 2 |
| 400057971 | 80688419 | 1 |
| 400057971 | 80688420 | 2 |
| 400057970 | 80688418 | 2 |
| 400057972 | 80688421 | 1 |
| 400057972 | 80688421 | 1 |
| 400057972 | 80688422 | 2 |
| 400057972 | 80688423 | 3 |
| 400057972 | 80688424 | 4 |
- Vijay_A_Verma4 years ago
Most Valuable Professional
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMDA1tzQ3UNJRsjAws7AwMTRXitUhTcICRcIQIWGJQ8LIgDijjBA6DEmWMMIlYYxLwkQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, #"Delivery no." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Purchase Order", Int64.Type}, {"Delivery no.", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"Purchase Order"}, {{"All", each _}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let DummyTbl = [All], output = Table.AddColumn(DummyTbl,"Result",each List.PositionOf(List.Distinct(DummyTbl[#"Delivery no."]),[#"Delivery no."])+1) in output), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Delivery no.", "Index", "Result"}, {"Delivery no.", "Index", "Result"}), #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Index", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Purchase Order", Int64.Type}, {"Delivery no.", Int64.Type}, {"Result", Int64.Type}}) in #"Changed Type1"- Anonymous4 years agoNot applicable
Thank you vijay, your solution is successfully worked.