Forum Discussion
derekmac
Helper I
Helper IMove 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 ...
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
AlienSx
Super User
Super UserHello, 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
derekmacHelper I
Helper IThanks 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 |