Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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...
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    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"

     

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    Thank you vijay, your solution is successfully worked.