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

     

dufoq3's avatar
dufoq3
Community Champion
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! 🙌