Forum Discussion
Helper IVgrouping values based on a reoccuring row
Hello All,
I have the following dataset:
| Brand | Value |
| Nike | |
| Adidas | |
| Average | |
Reebok
| |
Decat | |
| triatl |
I would like to be able to categorize all the brands above the line "Average" as "Direct" and all the ones below "Average" as "Indirect" in a new column.
The row where "Average" is, is not always the same. Sometimes, Average can be on row 4, sometimes on row 5 or 2, etc...
The end result would look like this:
| Brand | Value | Direct/Indirect |
| Nike | Direct | |
| Adidas | Direct | |
| Average | Average | |
| Reebok | Indirect | |
Decat | Indirect | |
| triatl | Indirect |
How can I achieve this?
Thanks for the help
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5PTlTSUTJVitWJVgouKMosSQVyTcBct8S8EoSkf1FiXnpmHkjAHCzgWJZalJgOUm4A1Z0I1+qcWFySA5IyA3NdgPrySuBcp9TUNLgxXqUFcG3hmXmpcAnX4pLEEoQZzhmJpSlp+UAnQRQZK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t, Price = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Price", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "RTM", each let i = Table.SelectRows(#"Added Index",each [Price]=0){0}[Index] in if [Index]<i then "Direct" else if [Index]=i then "" else "Indirect") in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
8 Replies
- lbendlin
Super User
please provide sample data with values. No need to add the Average line - that can be added automatically.
Consider a graphical solution (a chart) rather than a tabular solution.
ChrisjrHi lbendlin ,
Sorry, maybe I was not clear.
The "Average" is just a term and not a calculation.
Basically, I need to add a third column in which all the rows above the "Average" row will be in the "Direct" group and all the rows that come below the "Average" row will be in the "Indirect group" , knowing the "Average" is not always sitting in the same row.
- lbendlin
Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
- Chrisjr
Hi lbendlin , thanks for your reply.
Please see the sample data below or under this link : https://docs.google.com/spreadsheets/d/1RkD08r3ehC23vnBLEu2r1ZGmpQ_0ReP1/edit?usp=sharing&ouid=118319514467343036601&rtpof=true&sd=true
Brand Price Coca 5 Sprite 4 Fanta 5 Orangina 7 Average 0 Spa 4 Castle 6 Dinant 6 Beef 7 Jup 4 Wine 7 Estate 6 Chaudfontaine 3 The expected outcome should be as below:
Brand Price Direct/Indirect Coca 5 Direct Sprite 4 Direct Fanta 5 Direct Orangina 7 Direct Middle 0 Spa 4 Indirect Castle 6 Indirect Dinant 6 Indirect Beef 7 Indirect Jup 4 Indirect Wine 7 Indirect Estate 6 Indirect Chaudfontaine 3 Indirect You can see on the expected outcome, I need a third column "Direct/indirect" in which all the rows above the row containing the "Middle" term into the Brand column will be Direct, and all the rows below Middle will be Indirect.
The "Middle" term is not always on the same row number.
Thanks for your help,
Chris