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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bdpr_95
Helper I
Helper I

Create key column based on another column

Hello,

I have a table with a key column ("account_id"), however the number of digits in it is very large.

Do you know how to create a custom column where I can get the result below ("added_custom" column):

 

bdpr_95_1-1659522294135.png

 

 

Thank you!

1 ACCEPTED SOLUTION

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABEmSrE60UpOIKY5iDAE88HylhBBEN8ZxDQDEaYI9SZAwsgAwQebZwzmu0CkwJpiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [account_id = _t, quantity = _t, unit_price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"account_id", type text}, {"quantity", Int64.Type}, {"unit_price", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"account_id"}, {{"All", each _, type table [account_id=nullable text, quantity=nullable number, unit_price=nullable number, Index=number]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Rank", 1, 1, Int64.Type),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index1", "All", {"quantity", "unit_price", "Index"}, {"quantity", "unit_price", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded All",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

 

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

What logic to be followed to arrive at those numbers? 

For "added_custom" column numbers?

Basically a rank column logic. It started at number 1 (for example) and each time the account_id was repeated the power query had to indicate the number already created previously.
If the account_id is new then add +1 to the last number created.

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABEmSrE60UpOIKY5iDAE88HylhBBEN8ZxDQDEaYI9SZAwsgAwQebZwzmu0CkwJpiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [account_id = _t, quantity = _t, unit_price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"account_id", type text}, {"quantity", Int64.Type}, {"unit_price", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"account_id"}, {{"All", each _, type table [account_id=nullable text, quantity=nullable number, unit_price=nullable number, Index=number]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Rank", 1, 1, Int64.Type),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index1", "All", {"quantity", "unit_price", "Index"}, {"quantity", "unit_price", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded All",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors