# Tags
**Software** : [[02 - Tags/Software/Power Query]]
**Functions** used : [[Excel.Workbook]] [[Table.TransformColumnTypes]] [[List.Distinct]] [[Record.ToList]] [[List.Transform]] [[List.Split]] [[Table.ToColumns]] [[Table.Skip]] [[List.Count]] [[Table.FromColumns]] [[List.RemoveNulls]] [[Table.ExpandTableColumn]] [[Table.SelectRows]]
**Date** : 23-March-2025
# Description
- 🔍 Unlock the Magic of Power Query! Learn how to transform a pivot dataset into an unpivoted gem 📊💎. Watch as I demystify the process of converting column groups into rows 🧙♂️✨. Don't miss this data wizardry!
# Video Link
<iframe width="560" height="315" src="https://www.youtube.com/embed/lXdUQrgL0go?si=dzBcHuteL3ha1Z8o" 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 Source File Link](https://github.com/jbotes/powerbiTutorials/blob/main/PQ_RatingsData.xlsx)
[GITHUB Solution File Link](https://github.com/jbotes/powerbiTutorials/blob/main/PQ_ColumnGroups_BaSensei_Video.xlsx)
# M-Query
```mquery
let
Source = Excel.Workbook(File.Contents("C:\Users\PQ_RatingsData.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
ColumnNames = List.Distinct(Record.ToList(#"Changed Type"{1})),
RatingsListTable = List.Transform(List.Split(Table.ToColumns(Table.Skip(#"Changed Type",2)),List.Count(ColumnNames)), each Table.FromColumns(_, ColumnNames )),
RatingAgency = List.RemoveNulls(Record.ToList(#"Changed Type"{0})),
Custom1 = Table.FromColumns({RatingAgency} & {RatingsListTable},{"Ratings Agency", "Data"}),
#"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", ColumnNames),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each [Country] <> null and [Country] <> "")
in
#"Filtered Rows"
```
## Optional Tags
**Tags** :
**Technique** : [[Concatenation]]