Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago

Transpose Hierarchy from one state to another

Hello, I'm currently experiencing a problem with dataflows in power platform

I have an Excel file with a hierarchy in one line
eg:
ID lvl1 | Name lvl1 | ID lvl2 | Name lvl2 | ID lvl3 | Name lvl3

I'd like to transform this model into another structure using a dataflow.

 

The new structure is as follows:
ID | Name | ParentID

ParentID corresponds to the levels above that we saw in the first structure.

If you have any ideas, I'd love to hear from you. For the moment, the only solution I've found is this one:


- dupply each top level columns

- merge ID lvl | Name lvl | Parent lvl
- transpose columns
- split columns

 

But it's a lot of manual processing and the level structure can change over time (there may be new levels in the future) so my way of doing things requires modifications each time a new level is added.

 

Thank you 

2 Replies

  • slorin's avatar
    slorin
    Icon for Super User rankSuper User
    2 years ago

    Hi,

     

    = Table.FromRows(
       List.TransformMany(
          Table.ToRows(YourSource),
          each {0..2},
          (row, index)=> {row{index*2}, row{index*2+1}, try row{index*2-2} otherwise null}
       ),
       {"ID", "Name", "Parent ID"}
    )

     2 = max level -1

     

    Stéphane

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      2 years ago

      Unfortunately, I don't have access to the Table.FromRows method in a dataflow