title: Convert SAP BI hierarchy to flat table
body: <p>I have a table:<br>
  <a href="https://i.stack.imgur.com/GqxoT.jpg" rel="nofollow noreferrer">Old table</a>
  <a href="https://i.stack.imgur.com/GqxoT.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GqxoT.jpg" alt="Old table"></a>  </p>
  
  <p>Level 1 is top level. CC is the smallest unit inside our company.</p>
  
  <p>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, </p>
  
  <p>e.g. <code>100111 |CC |3 |IS// |IS/ |IS</code>.</p>
  
  <p><a href="https://i.stack.imgur.com/9wlcX.jpg" rel="nofollow noreferrer">New Table</a>
  <a href="https://i.stack.imgur.com/9wlcX.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9wlcX.jpg" alt="New Table"></a>  </p>
  
  <p>Using Excel I can do it easily by using some conditional formula and copy the cell above if current cell is <code>CC</code>.  </p>
  
  <p>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:</p>
  
  <pre><code>If(B2=1;A2;D1)
  </code></pre>
  
  <p>and i fill it down for the rest. In my data, the first row is always level 1.
  For Level 2:</p>
  
  <pre><code>=IF(B2=2(//because is Level 2),A2,IF(B2&lt;2,"",E1))
  </code></pre>
  
  <p>And i repeat the same formula for other Column.</p>
  
  <p>Can someone suggest me a solution for this problem? </p>