Forum Discussion
Add Rows - Loop
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!
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
6 Replies
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 CombinedAllIf 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:
