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

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!

Need Help

AlienSx

2 years ago

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

6 Replies

AlienSx

Super User

2 years ago

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

Helper I

2 years ago

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

Super User

2 years ago

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

mlsx4

Memorable Member

2 years ago

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
Helper I
2 years ago
Thank you

Forum Discussion

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