Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 1
c a 1
c b 2
c c 3

2 ACCEPTED SOLUTIONS

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"

 

 

View solution in original post

Anonymous
Not applicable

Thank you vijay, your solution is successfully worked.

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

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
Not applicable

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 1Column 2Column 3
Purchase OrderDelivery no.Result
400057970806884171
400057970806884171
400057970806884182
400057971806884191
400057971806884202
400057970806884182
400057972806884211
400057972806884211
400057972806884222
400057972806884233
400057972806884244

 

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
Not applicable

Thank you vijay, your solution is successfully worked.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors