Create a calculated Brand_Id Column based on the brand name found in the row.

[ashishoza12345]

I have given below table:

I have to create a brand_id for each individual Brand as given below:

Please help!

[v-yangliu-msft]

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

[PhilipTreacy · Accepted solution]

Hi @ashishoza12345 

Download sample PBIX file

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