Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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" ...
Need Help
  • 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 (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"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

What logic to be followed to arrive at those numbers? 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    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 (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"