Forum Discussion

derekmac's avatar
derekmac
Icon for Helper I rankHelper I
2 years ago
Solved

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 ...
Need Help
  • AlienSx's avatar
    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
mlsx4's avatar
mlsx4
Icon for Memorable Member rankMemorable 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's avatar
derekmac
Icon for Helper I rankHelper I
2 years ago

Thank you