Power Query Matrix As Power Automate HTML Table

[tsimpkins]

Hi,

I have created a Power Automate Flow that uses a Power Query to Get the data set from a Matrix visual in a Power Bi report. It then takes the data set and creates an HTML Table. The issue I have is I need to fully expand all 3 of the nested groupings for all data in the Matrix through the query. Currently it seems it nees to be manually done on the Matrix visual in the Power Bi report and so sometimes, when new non-expanded main groups are added to the data set, the data will come in unexpanded for certain groups.

Grouping A	Grouping B	Grouping C	Summary 1	Summary 2
Group 1	Group 1.A	Group 1.A.1	50	100
Group 1	Group 1.A	Group 1.A.2	75	150
Group 1	Group 1.A	Group 1.A	125	250
Group 1	Group 1.B	Group 1.B.1	275	300
Group 1	Group 1.B	Group 1.B	275	300
Group 1	Group 1	Group 1	400	550
Group 2	Group 2	Group 2	500	300
Group 3	Group 3.A	Group 3.A.1	100	150
Group 3	Group 3.A	Group 3.A	100	150
Group 3	Group 3.B	Group 3.B.1	200	200
Group 3	Group 3.B	Group 3.B	200	200
Group 3	Group 3	Group 3	300	350

Group 2 has records in the other Groupings if they have a summary number but those records aren't coming through in the data set, only the Summary/Top Level row of that grouping. Is there a way I can default the Matrix to having all levels/groups expanded/open regardless of the data being presented?

[v-pgoloju]

Hi @tsimpkins 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[lbendlin]

1. "Borrow"  the query from the matrix visual (for example via Performance Analyzer or the Excel "live connection")

2. clean the query up (for example in DAX Studio, or in the DAX Query view). Think hard, think twice if you really really want these rollups.

3. Run the query in your flow. Make sure to include nulls (important!)

4. convert the "First Table Rows"  output to a HTML table

5. include that table in your email body.

[tsimpkins]

Those steps are exactly what I did in PA to make the table. Part of the problem was though that the matrix I borrowed the query from wouldn't be totally expanded so some groups would come into the PA without the sub-groupings of some groups. The "Expand All" looks to be the best solution for this issue.

[v-pnaroju-msft · Accepted solution]

Hi @tsimpkins,

We sincerely appreciate your inquiry on the Microsoft Fabric Community Forum.

To expand all levels in the Matrix visual within Power BI Desktop, right-click on any row header and select Expand all. Once you have done this, saving and publishing the report should retain the expanded state.

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.