Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have the following table:
AREA DOMAIN SUBDOMAIN TEACHER ASSISTANT
1 1 1 Joe Karen
1 1 2 Frank Tom
1 2 7 Mia Bobby
2 3 4 Lori Karen
2 3 5 Mia Frank
2 4 9 Mary Adam
I'm trying to get it so AREA/DOMAIN/SUBDOMAIN dont repeat so it looks like:
AREA DOMAIN SUBDOMAIN TEACHER ASSISTANT
1 1 1 Joe Karen
2 Frank Tom
1 2 7 Mia Bobby
2 3 4 Lori Karen
5 Mia Frank
4 9 Mary Adam
Here is an example (In this case since Area and Domain are the same, then it only shows once and doesn't repeat)
AREA DOMAIN SUBDOMAIN TEACHER ASSISTANT
1 1 1 Joe Karen
2 Frank Tom
@EaglesTony then reshaping them in Power Query is the right solution, isnt it? btw you may need to use table in reporting.
I can group them in PowerQuery, but it repeats the Domain and SubDomain.
Can you show me your Grouped results and be more specific on how these two are repeating?
I group by Area,Domain,SubDodomain and it does the grouping correctly, but the results are:
AREA DOMAIN SUBDOMAIN TEACHER ASSISTANT
1 1 1 Joe Karen
1 1 2 Frank Tom
1 2 7 Mia Bobby
2 3 4 Lori Karen
2 3 5 Mia Frank
2 4 9 Mary Adam
So the first 3 columns solves the group, but the 3 columns mentioned are repeated, so it should show the first time those columns, but next one that is similiar, leave that blank.
What I am wondering is if I can somehow have another column such as
AREA DOMAIN SUBDOMAIN TEACHER ASSISTANT LEVEL
1 1 1 Joe Karen 1
1 1 2 Frank Tom 2
1 2 7 Mia Bobby 1
2 3 4 Lori Karen 1
2 3 5 Mia Frank 2
2 4 9 Mary Adam 1
Then do some logic if not level 1, replace the column value with blank.
Did you try the initial approach i offered step by step in this thread?
You would need to add 3 separate Custom columns for 'AREA, 'DOMAIN', 'SUBDOMAIN' each to make repeating rows null by M coding.
They are blank when duplicated
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
7 |