Forum Discussion

GabinAM's avatar
GabinAM
Frequent Visitor
2 years ago
Solved

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) a...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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's avatar
GabinAM
Frequent Visitor
2 years ago

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's avatar
dufoq3
Community Champion
2 years ago

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's avatar
    GabinAM
    Frequent Visitor
    2 years ago

    Many thanks dufoq3 

    Let's consider it solved! 👌

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      OK 🙂

  • GabinAM's avatar
    GabinAM
    Frequent Visitor
    2 years ago

    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! 🙌