Search code examples
excelpowerqueryhierarchysap-bw

Convert SAP BI hierarchy to flat table

I have the below "old" table where Level "1" is top level, "CC" is the smallest unit inside our company:

Old table

What I want to do is convert this table into a flat table with additional column like level 1 / level 2 / Level 3, which show parent department of each node,

e.g. 100111 |CC |3 |IS// |IS/ |IS.

New Table

Using Excel I can do it easily by using some conditional formula and copy the cell above if current cell is CC.

My process is like this: SAP Application (export) -> .xls file (without Level and Parent Columns) -> creating new column for level and parent node with power query -> make new column (level 1 - 6) like example in the new table.

For Column Level "1", I use this formula:

If(B2=1;A2;D1)

and I fill it down for the rest. In my data, the first row is always level 1.

For Level 2:

=IF(B2=2(//because is Level 2),A2,IF(B2<2,"",E1))

And I repeat the same formula for other Column.

Can someone suggest me a solution for this problem?

Solution

  • I think the Power Query equivalent of your first formula would be to Add a Column with this formula:

    if [Level] = 1 then [Department] else null

    I would follow that with a "Fill / Down" step (from the Transform ribbon).

    The subsequent formulas would look similar, e.g. for Level 2

    if [Level] = 2 then [Department] else null

    Follow each with a "Fill / Down" step and you should be done.