Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have given below table:
I have to create a brand_id for each individual Brand as given below:
Please help!
Solved! Go to Solution.
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
Proud to be a Super User!
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
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |