Add Rows - Loop

[GabinAM]

Hello everyone! 😊

Hope you're doing well! 

Initial Situation

I'm currently working on tricky DB, please see below an example, wtih some informations stored in columns (ID/Date/Underlying) and some others vertically (Label/Level) (it's a bit hard to express clearly). 

ID	Label	Level	Date	Underlying
1	Accumulation	300	01/01/2024	Potato
1	Barrier	400	01/01/2024	Potato
2	Up	300	03/01/2024	Green Beans
2	Down	400	03/01/2024	Green Beans
2	Middle	450	03/01/2024	Green Beans
3	Up	300	05/01/2024	Carrots
3	Down	400	05/01/2024	Carrots
3	Middle	350	05/01/2024	Carrots
3	Barrier	500	05/01/2024	Carrots

Expected Result

I would like to display a table or matrix in PowerBI (based on a ID filter) as folow: 

Example 1 

ID	Accumulation	Barrier	Date	Underlying
1	300	400	01/01/2024	Potato

Example 2 

ID	Up	Down	Middle	Date	Underlying
2	300	400	450	03/01/2024	Green Beans

Example 3

ID	Up	Down	Middle	Barrier	Date	Underlying
3	300	400	350	14/05/1901	05/01/2024	Carrots

However...

So far, I'm a bit stuck as I cannot reach this result either with a Table or with a Matrix. Please see below an example: 

*******************************************

In order to reach my result, I was thinking about creating a Looping Querry adding for each ID two additional rows as follow : 

ID	Label	Level	Date	Underlying
1	Accumulation	300	01/01/2024	Potato
1	Barrier	400	01/01/2024	Potato
1	Date	01/01/2024
1	Underlying	Potato
2	Up	300	03/01/2024	Green Beans
2	Down	400	03/01/2024	Green Beans
2	Middle	450	03/01/2024	Green Beans
2	Date	03/01/2024
2	Underlying	Green Beans
3	Up	300	05/01/2024	Carrots
3	Down	400	05/01/2024	Carrots
3	Middle	450	05/01/2024	Carrots
3	Barrier	500	05/01/2024	Carrots
3	Date	05/01/2024
3	Underlying	Carrots

I don't know if it's managable on PowerQuerry or if there is a easier way to reach the expected result in PowerBI. Any help would be greatly appreaciated! 🙏

Please find here the PBIX and Excel used for the example. 

Many thanks in advance! 

[dufoq3 · Accepted solution]

Hi @GabinAM, Power Query solution:

Result (you can delete last step if you want to see separate tables)

let
    Source = Excel.Workbook(File.Contents("Z:\support\Execution\GAM\Control - Check - Data\20240408 - ADM New DB\Test 2\TEST.xlsx"), true, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    ChangedType = Table.TransformColumnTypes(Sheet1_Sheet,{{"ID", Int64.Type}, {"Label", type text}, {"Level", Int64.Type}, {"Date", type date}, {"Underlying", type text}}),
    GroupedRows = Table.Group(ChangedType, {"ID"}, {{"All", each Table.Pivot(_, List.Distinct([Label]), "Label", "Level"), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[GabinAM]

Hello @dufoq3 it works wonderfully! 

I'm just checking how can we hide the column if the value is null for the relevant ID? 

Basicly, I have ~ 15/20 different ID with differents labels so it's not very handy to keep all of them on the table... 

[dufoq3]

If you want to achive this, remove my Power Query code and use the one you uploaded as an example. Then you can use matrix like this:

[GabinAM]

Hi @dufoq3 just to follow on this topic, you can display only the columns for the selected iteams by selecting the columns in PowerQuerry and then Unpivot Only Selected Columns.

let
Source = Excel.Workbook(File.Contents("Z:\support\Execution\GAM\Control - Check - Data\20240408 - ADM New DB\Test 2\TEST.xlsx"), true, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
ChangedType = Table.TransformColumnTypes(Sheet1_Sheet,{{"ID", Int64.Type}, {"Label", type text}, {"Level", Int64.Type}, {"Date", type date}, {"Underlying", type text}}),
GroupedRows = Table.Group(ChangedType, {"ID"}, {{"All", each Table.Pivot(_, List.Distinct([Label]), "Label", "Level"), type table}}),
CombinedAll = Table.Combine(GroupedRows[All]),
#"Unpivoted Only Selected Columns" = Table.Unpivot(CombinedAll, {"Accumulation", "Barrier", "Up", "Down", "Middle", "Higher", "Lower", "Haut", "Bas"}, "Attribute", "Value")
in
#"Unpivoted Only Selected Columns"

Then in a Matrix. 

I guess you know this functionnality but just in case someone need it in the future!

Again many thanks for your help! 🙌

[GabinAM]

Many thanks @dufoq3 

Let's consider it solved! 👌

[dufoq3]

OK 🙂