Forum Discussion
ashishoza12345
Helper I
Helper ICreate a calculated Brand_Id Column based on the brand name found in the row.
I have given below table:
I have to create a brand_id for each individual Brand as given below:
Please help!
How many branbds do you have to do this for? If it's not many you can set up a record to hold the Brand and Brand ID pairs. Then use this query
let Brand_ID = [#"Honda" = 1, #"Suzuki" = 2, #"Ford" = 3, #"Toyota" = 4], Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sjPS0lUitWJVgourSrNzgQz3fKLUsCMkPzK/BKINEIhhmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Brand ID", each Record.Field(Brand_ID, [Brand])) in #"Added Custom"Regards
Phil
2 Replies
- PhilipTreacy
Super User
How many branbds do you have to do this for? If it's not many you can set up a record to hold the Brand and Brand ID pairs. Then use this query
let Brand_ID = [#"Honda" = 1, #"Suzuki" = 2, #"Ford" = 3, #"Toyota" = 4], Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sjPS0lUitWJVgourSrNzgQz3fKLUsCMkPzK/BKINEIhhmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Brand ID", each Record.Field(Brand_ID, [Brand])) in #"Added Custom"Regards
Phil
Hi ashishoza12345 ,
Here are the steps you can follow：
1. In Power query, Add Column – Index Column – From 1.
2. Create calculated column.
Flag = RANKX(FILTER(ALL('Table'),'Table'[Brand]=EARLIER('Table'[Brand])),[Index],,ASC)Brand_ID = var _id=CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),[Flag]=1&&[Brand]=EARLIER('Table'[Brand]))) return IF([Flag]=1,[Index],_id)3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
