# Tags
**Software** : [[Power Query]]
**Functions** used : [[Excel.CurrentWorkbook]] [[Table.UnpivotOtherColumns]] [[Table.TransformColumns]] [[Text.Contains]] [[Table.Combine]] [[Table.Group]] [[Table.AddColumn]] [[Table.Pivot]] [[Table.RemoveColumns]] [[List.Distinct]] [[Table.FromColumns]] [[Table.ColumnNames]] [[List.Transform]]
**Date** : 25-March-2026
# Description
In this video i show you some fancy power query tricks to group and transform a table
# Video Link
<iframe width="560" height="315" src="https://www.youtube.com/embed/0-G-3IZYBzU?si=yeMg6lZH8wd6HSH8" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>
[GITHUB File Link](https://github.com/jbotes/powerbiTutorials/blob/main/Pq_Advanced_TableSyummary_video.xlsx)
# M-Query
```mquery
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Table.FillDown(Source, {"Company"}), {"Company", "Dept"}, "Attr", "Values"),
RevCost = Table.TransformColumns(Unpivot, {"Attr", each "Total " & (if Text.Contains(_, "Rev") then "Revenue" else "Cost")}),
Grouping =
// combine tables
Table.Combine(
//grouping
Table.Group(
RevCost,
{"Company"},
{{"tbl",
each
Table.AddColumn(
//pivot
Table.Pivot(
//remove column
Table.RemoveColumns(_, "Dept"),
List.Distinct(_[Attr]),
"Attr",
"Values", List.Sum),
"Total Profit",
each [Total Revenue] - [Total Cost])
}})[tbl]),
Custom1 = Table.FromColumns(List.Transform(Table.ToColumns(Grouping), each _ & {try List.Sum(_) otherwise "GrandTotal"}), Table.ColumnNames(Grouping))
in
Custom1
```
## Optional Tags
**Tags** :
**Technique** : [[Error Try]]