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

[EaglesTony]

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

[v-kpoloju-msft]

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.

[EaglesTony]

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.

[v-kpoloju-msft]

Hi @EaglesTony,

Has your issue been resolved? If the response provided by the community member @MasonMA, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding.

[EaglesTony]

No, I am still trying to find a solution to get my data to come out as follows:

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

[v-kpoloju-msft]

Hi @EaglesTony,

Has your issue been resolved? If the response provided by the community member @MasonMA, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding.

[MasonMA]

@EaglesTony 

For sure there are different approaches to the same problem and I'd be happy to hear your solution. 

In the meantime can you please tell what differences you see between your expected result and that on this picture? 

 

[MasonMA]

Please post your solution once done, thanks. 

[FBergamaschi]

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

[EaglesTony]

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.

[MasonMA]

@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:) 

[EaglesTony]

It is giving me an error on 

 AddIndex

Saying it wasn't recognized. 

[MasonMA]

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

[EaglesTony]

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.

[MasonMA]

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. 

[EaglesTony]

Actually the index field works as the sort.

Appears to be in the order I want, I just need now to see if I can create a paginated report out of this, since it has to be in this order for a load into Excel spreadsheet automatically (via PowerAutomate).

[EaglesTony]

This might work, but AREA, DOMAIN,SUBDOMAIN can be alphanumeric.

What I am thinking is if the row has AREA,DOMAIN,SUBDOMAIN filled in make this some number (i.e 1), if AREA,DOMAIN filled in make this 2 and if only DOMAIN filled in make it 3 and then use this new field in the calculation you mentioned.

So going to try to get unique values for each of the 3, assign a value to them and come up with a unique number sequence that makes sense.

Also, wondering, since the data is in the order I want in PowerQuery, is there a way to use the actual row number in some sort of custom column called "sorted" ?

[EaglesTony]

Getting this error:

[MasonMA]

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 🙂 

[EaglesTony]

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

[MasonMA]

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