Add rows

[kassymov_su]

Hi All,

What is the fastest way to add rows in power query if?

In example below I added manually only three rows for each group just to demonstrate what I want, but in fact I have many gorups. Moreover, when add row I need to sum the column [Volums] for each group.

I know one way to do that, and it is by using Table.insertRows but then I need to mention exact row and it takes a lot of time still

[v-jingzhang]

Hi @kassymov_su 

I agree with @BA_Pete. In Power Query, the data table is expected to be a flat table. It cannot do something like merging cells in Excel. As what you want is sub totals of groups, if you want to display this output in Power BI report, you can use Matrix visual to do that. Put both Area code and Activities columns in Rows fields, then expand them. You will have sub total rows there. 

Create a matrix visual in Power BI - Power BI | Microsoft Docs

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[BA_Pete]

Hi @kassymov_su ,

This should absolutely not be done in Power Query.

The correct way to do what you want would be to create a dimension table that contains unique [Code for Area] values with your desired descriptions in another column, something like this:

|  Code for Area  |  descForArea                  |
|  Civil-A1       |  Civil Works at Area A1       |
|  Electrocal-A1  |  Electrical Works at Area A1  |
|  ...

You would then relate dimension[Code for Area] ONE : MANY fact[Code for Area] and use dimension[descForArea] in a matrix visual as the Rows value.

There's a few other settings that you can adjust to get exactly the look that you want, such as Stepped Layout and Stepped Indentation, but this is the basic setup that you need to work toward.

Pete