Move Values to the Left in Columns Side by Sides, removing Nulls

[derekmac]

Hi All

I have a set of fields as below

Manager4_Name	Manager3_Name	Manager2_Name	Manager1_Name	ReportingTo	Fullname
null	null	null	null	Jane Upton	Nicole Peters
null	null	null	Aaron John	Aaron John	Jan Orchard
null	null	Aaron John	Pam Clement	Pam Clement	Elliot Paul
null	Adam Johnson	Anna Kournikova	Adiam Ahmad	Adiam Ahmad	Azir Mohamed
Darren Jones	Alan Patel	Paul Smith	Alan Smith	Alan Smith	Alexandra Inniss

I would like to change this to the following format in Power Query

Manager4_Name	Manager3_Name	Manager2_Name	Manager1_Name	ReportingTo	Fullname
Jane Upton	Nicole Peters
Aaron John	Aaron John	Jan Orchard
Aaron John	Pam Clement	Pam Clement	Elliot Paul
Adam Johnson	Anna Kournikova	Adiam Ahmad	Adiam Ahmad	Azir Mohamed
Darren Jones	Alan Patel	Paul Smith	Alan Smith	Alan Smith	Alexandra Inniss

So basically remove all Nulls and move the values across so line up the levels

All help appreciated!

[mlsx4]

Hi @derekmac 

Add this step from your last step (remember to replace #"Headers promoted" by the name of your last step):

= Table.ReverseRows(Table.FromRecords(List.Transform(List.Reverse(List.Transform(Table.ToRows(#"Headers promoted"),List.RemoveNulls)),each Record.FromList(_,List.FirstN(Table.ColumnNames(#"Headers promoted"),List.Count(_)))),null,MissingField.UseNull))

[derekmac]

Thank you

[AlienSx]

Hello, @derekmac 

let
    Source = your_table,
    to_rows = List.Buffer(Table.ToRows(Source)),
    count = Table.ColumnCount(Source),
    txform = List.Transform(
        to_rows, 
        (x) => 
            [a = List.RemoveNulls(x),
            b = a & List.Repeat({null}, count - List.Count(a))][b]
    ),
    z = Table.FromRows(txform, Table.ColumnNames(Source))
in
    z

[derekmac]

Thanks a Lot

Manager4_Name	Manager3_Name	Manager2_Name	Manager1_Name	ReportingTo	Fullname	ID
null	null	null	null	Jane Upton	Nicole Peters	1
null	null	null	Aaron John	Aaron John	Jan Orchard	2
null	null	Aaron John	Pam Clement	Pam Clement	Elliot Paul	3
null	Adam Johnson	Anna Kournikova	Adiam Ahmad	Adiam Ahmad	Azir Mohamed	4
Darren Jones	Alan Patel	Paul Smith	Alan Smith	Alan Smith	Alexandra Inniss	5

I omitted the ID Column which I would like to stay where it is, is that possible?

Manager4_Name	Manager3_Name	Manager2_Name	Manager1_Name	ReportingTo	Fullname	ID
Jane Upton	Nicole Peters					1
Aaron John	Aaron John	Jan Orchard				2
Aaron John	Pam Clement	Pam Clement	Elliot Paul			3
Adam Johnson	Anna Kournikova	Adiam Ahmad	Adiam Ahmad	Azir Mohamed		4
Darren Jones	Alan Patel	Paul Smith	Alan Smith	Alan Smith	Alexandra Inniss	5

[AlienSx · Accepted solution]

let
    Source = your_table,
    to_rows = List.Buffer(Table.ToRows(Source)),
    count = Table.ColumnCount(Source) - 1,
    txform = List.Transform(
        to_rows, 
        (x) => 
            [a = List.RemoveNulls(List.RemoveLastN(x, 1)),
            b = a & List.Repeat({null}, count - List.Count(a)) & {List.Last(x)}][b]
    ),
    z = Table.FromRows(txform, Table.ColumnNames(Source))
in
    z

[derekmac]

Thanks a lot