Need help with Power Query - Creating new rows for non-ending team members

[Saikumarkatkuri]

I'm working on a Power Query transformation in Power BI, and I'm facing a challenge that I need assistance with. I have a table with four columns: L0, L1, L2, and L3, representing hierarchical levels in a team structure.

Here is a sample of the original data:

L0 L1 L2 L3 chris damien lucia sai chris damien lucia rashim chris damien rachel
chris edwin
chris lisa mayur
chris lisa mayur geetha

->>> 
let
Source = Table.FromRows({
{"chris", "damien", "lucia", "sai"},
{"chris", "damien", "lucia", "rashim"},
{"chris", "damien", "rachel", null},
{"chris", "edwin", null, null},
{"chris", "lisa", "mayur", null},
{"chris", "lisa", "mayur", "geetha"}
}, type table [L0 = text, L1 = text, L2 = text, L3 = text])
in
Source <----

I want to create new rows for the missing team members, so the table reflects the complete hierarchy. For example, in the above sample data, Lucia and Damien have missing rows in L3, and Lisa is missing in L2, and Chris missing in L0

The desired output should look like this:

L0 L1 L2 L3
chris damien lucia sai
chris damien lucia rashim
chris damien lucia
chris damien
chris edwin
chris lisa mayur
chris lisa mayur geetha
chris lisa
chris

Could someone please guide me on how to achieve this transformation using Power Query? Any insights, suggestions, or sample code would be greatly appreciated.

[slorin · Accepted solution]

Hi

= Table.Distinct(
   Table.FromRows(
    List.TransformMany(
     Table.ToRows(Source),
     each {1..4},
     (x,y) => List.FirstN(x,y) & List.Repeat({null}, 4-y)
    ),
  type table [L0 = text, L1 = text, L2 = text, L3 = text])
)

Stéphane