topic Re: How can I get a table to collapse so certain columns only appear when they change in DAX Commands and Tips

How can I get a table to collapse so certain columns only appear when they change

EaglesTony — Mon, 14 Jul 2025 12:24:57 GMT

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

Re: How can I get a table to collapse so certain columns only appear when they change

FBergamaschi — Mon, 14 Jul 2025 12:39:38 GMT

Create a Matrix in Power BI Desktop and include the columns in the columns section of the Matrix

See attached picture

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

Re: How can I get a table to collapse so certain columns only appear when they change

EaglesTony — Mon, 14 Jul 2025 12:53:02 GMT

I'm not sure that will fit my needs..I need the rows to be be blank when duplicated, as this data will eventually be used as a paginated report for export.

Re: How can I get a table to collapse so certain columns only appear when they change

FBergamaschi — Mon, 14 Jul 2025 13:05:56 GMT

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

Re: How can I get a table to collapse so certain columns only appear when they change

MasonMA — Mon, 14 Jul 2025 13:22:35 GMT

EaglesTony

Hi, is this what you are looking to have in report?

If so, you may need to reshape the data a bit in Power Query.

1. Add index as below

2. For each of AREA, DOMAIN, SUBDOMAIN, create a new 'Custom' column with below code:

if [Index] = 0 then Text.From([AREA]) else if [AREA] = AddIndex{[Index]-1}[AREA] then null else Text.From([AREA])

if [Index] = 0 then Text.From([DOMAIN]) else if [DOMAIN] = AddAreaDisplay{[Index]-1}[DOMAIN] and [AREA] = AddAreaDisplay{[Index]-1}[AREA] then null else Text.From([DOMAIN])

if [Index] = 0 then Text.From([SUBDOMAIN]) else if [SUBDOMAIN] = AddDomainDisplay{[Index]-1}[SUBDOMAIN] and [DOMAIN] = AddDomainDisplay{[Index]-1}[DOMAIN] and [AREA] = AddDomainDisplay{[Index]-1}[AREA] then null else Text.From([SUBDOMAIN])

On UI it will look like below

3. Remove the original AREA/DOMAIN/SUBDOMAIN and Index Column.

Load them in Power BI for reporting.

Hope it helps:)

Re: How can I get a table to collapse so certain columns only appear when they change

EaglesTony — Mon, 14 Jul 2025 13:29:44 GMT

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

Re: How can I get a table to collapse so certain columns only appear when they change

MasonMA — Mon, 14 Jul 2025 13:35:39 GMT

EaglesTony then reshaping them in Power Query is the right solution, isnt it? btw you may need to use table in reporting.

Re: How can I get a table to collapse so certain columns only appear when they change

EaglesTony — Mon, 14 Jul 2025 14:02:20 GMT

I can group them in PowerQuery, but it repeats the Domain and SubDomain.

Re: How can I get a table to collapse so certain columns only appear when they change

MasonMA — Mon, 14 Jul 2025 14:23:19 GMT

EaglesTony

Can you show me your Grouped results and be more specific on how these two are repeating?

Re: How can I get a table to collapse so certain columns only appear when they change

EaglesTony — Mon, 14 Jul 2025 14:32:48 GMT

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.

Re: How can I get a table to collapse so certain columns only appear when they change

MasonMA — Mon, 14 Jul 2025 14:38:46 GMT

EaglesTony

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.

Re: How can I get a table to collapse so certain columns only appear when they change

EaglesTony — Mon, 14 Jul 2025 15:18:51 GMT

It is giving me an error on

 AddIndex

Saying it wasn't recognized.

Re: How can I get a table to collapse so certain columns only appear when they change

MasonMA — Mon, 14 Jul 2025 16:42:27 GMT

I extracted M code in all steps UI generated from Query Editor. You can copy and paste it into yours for more testing.

let Source = Table.FromRows({ {"1", "1", "1", "Joe", "Karen", "2024-01-01", "Math", "2"}, {"1", "1", "2", "Frank", "Tom", "2024-01-01", "Science", "1"}, {"1", "2", "7", "Mia", "Bobby", "2024-01-02", "English", "3"}, {"2", "3", "4", "Lori", "Karen", "2024-01-03", "Math", "2"}, {"2", "3", "5", "Mia", "Frank", "2024-01-03", "History", "1"}, {"2", "4", "9", "Mary", "Adam", "2024-01-04", "Math", "4"} }, {"AREA", "DOMAIN", "SUBDOMAIN", "TEACHER", "ASSISTANT", "DATE", "SUBJECT", "HOURS"}), ChangedTypes = Table.TransformColumnTypes(Source, { {"AREA", Int64.Type}, {"DOMAIN", Int64.Type}, {"SUBDOMAIN", Int64.Type}, {"TEACHER", type text}, {"ASSISTANT", type text}, {"DATE", type date}, {"SUBJECT", type text}, {"HOURS", Int64.Type} }), AddIndex = Table.AddIndexColumn(ChangedTypes, "Index", 0, 1, Int64.Type), AddColumns = Table.AddColumn(AddIndex, "AREA_Display", each if [Index] = 0 then Text.From([AREA]) else if [AREA] = AddIndex{[Index]-1}[AREA] then null else Text.From([AREA])), AddDomainDisplay = Table.AddColumn(AddColumns, "DOMAIN_Display", each if [Index] = 0 then Text.From([DOMAIN]) else if [AREA] = AddIndex{[Index]-1}[AREA] and [DOMAIN] = AddIndex{[Index]-1}[DOMAIN] then null else Text.From([DOMAIN])), AddSubdomainDisplay = Table.AddColumn(AddDomainDisplay, "SUBDOMAIN_Display", each if [Index] = 0 then Text.From([SUBDOMAIN]) else if [AREA] = AddIndex{[Index]-1}[AREA] and [DOMAIN] = AddIndex{[Index]-1}[DOMAIN] and [SUBDOMAIN] = AddIndex{[Index]-1}[SUBDOMAIN] then null else Text.From([SUBDOMAIN])), RemovedOriginals = Table.RemoveColumns(AddSubdomainDisplay, {"AREA", "DOMAIN", "SUBDOMAIN"}), Renamed = Table.RenameColumns(RemovedOriginals, { {"AREA_Display", "AREA"}, {"DOMAIN_Display", "DOMAIN"}, {"SUBDOMAIN_Display", "SUBDOMAIN"} }), Final = Table.RemoveColumns(Renamed, {"Index"}) in Final

Re: How can I get a table to collapse so certain columns only appear when they change

EaglesTony — Mon, 14 Jul 2025 17:25:35 GMT

Getting this error:

Re: How can I get a table to collapse so certain columns only appear when they change

MasonMA — Mon, 14 Jul 2025 17:35:29 GMT

That's because when Power Query couldnt find the previous step 'AddIndex' because it either doesn’t exist yet or is being referenced before it was created.

I'd suggest you copy and paste my entire M codes into a new 'blank query' and understand the logic of each step. Once you got this you can adjust your code either through UI or Query editor 🙂

Re: How can I get a table to collapse so certain columns only appear when they change

EaglesTony — Mon, 14 Jul 2025 18:12:35 GMT

This sort of works, meaning that it looks right in the PowerQuery but when I try to report on this

it is showing some AREA as blank, followed by 1 and then 2, it is almost like the display needs to tie these records to their parents, for the sort order to properly happen.

Re: How can I get a table to collapse so certain columns only appear when they change

MasonMA — Mon, 14 Jul 2025 19:45:02 GMT

Exactly! Power BI visuals like tables or matrix sort based on actual values, not visual blanks.

I asked Copilot, and the idea it provides is we would need to keep your original columns and create one new Column to help you sort.

Table.AddColumn(AddSubdomainDisplay, "Sort", each [AREA] * 10000 + [DOMAIN] * 100 + [SUBDOMAIN])

In report you need to add this 'Sort' column in the table and then figure out a way to 'Hide' it. the way i tried is: Use 'Minimal' for Style, Remove the Column Names, and White out the Values in the table. It will look like below.

Re: How can I get a table to collapse so certain columns only appear when they change

v-kpoloju-msft — Tue, 15 Jul 2025 07:12:58 GMT

Hi EaglesTony,

Thank you for reaching out to the Microsoft fabric community forum. Aslo thanks to MasonMA, FBergamaschi, for those inputs on this thread. I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.

Outcome:

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

Re: How can I get a table to collapse so certain columns only appear when they change

EaglesTony — Tue, 15 Jul 2025 11:09:28 GMT

Hi,

Thanks for that, but I need it the exact following order, since this data will eventually be used in an excel report as input, thus the need to have it as :

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

I'm still going to try to figure out some way to make parent/child relationships.

Re: How can I get a table to collapse so certain columns only appear when they change

MasonMA — Tue, 15 Jul 2025 11:17:44 GMT

Please post your solution once done, thanks.