What about assigning unique values with multiple criteria in power BI as like , for instance.

[Anonymous]

I am assigning unique values for each of the subsets (first letter is a, b, c). I am able to separate out the two parts in the first column like this:

a a 1
a b 2
a a 1
b a 1
b b 2
b c 3
c a 1
c a 1
c b 2
c c 3

[Vijay_A_Verma · Accepted solution]

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"

[Vijay_A_Verma]

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. 

[Anonymous]

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_Verma · Accepted solution]

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"

[Anonymous · Accepted solution]

Thank you vijay, your solution is successfully worked.