Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

collapse columns together

i want to merge columns together

 

i have500 rows with 6 columns, if column 1,2,3 have data then columns 4,5,6 are null.  If Columns 4,5,6 have data, then columns 1,2,3 are null

 

how can i collapse or merge these 6 columns that i end up with 3 columns with any nulls replaced with data in either columns

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    thanks, i ended up doing a if col is not null then col, else if col1 is not null col1 else if col2 is not null col2

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    Hi Anonymous,

     

    The most secure way is to test for each row if all cells are empty (= null)

     

    Give this a go, you can copy the full script into a new blank query.

    let
    Source = Table.FromColumns(
        {
            {1..5} & List.Repeat( { null }, 4),
            {"1".."5"} & List.Repeat( { null }, 4),
            {"a".."e"} & List.Repeat( { null }, 4),
            List.Repeat( { null }, 5) & {6..9},
            List.Repeat( { null }, 5) & {"6".."9"},
            List.Repeat( { null }, 5) & {"f".."i"}
        }
    ),
    TableA = Table.SelectRows(
        Table.SelectColumns(Source, List.FirstN( Table.ColumnNames(Source), Number.RoundAwayFromZero( Table.ColumnCount(Source)/2, 0)) ),
        each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
    ),
    TableB = Table.SelectRows(
        Table.SelectColumns(Source, List.LastN( Table.ColumnNames(Source), Number.RoundAwayFromZero( Table.ColumnCount(Source)/2, 0)) ),
        each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
    ),
    Combined = TableA & Table.RenameColumns( TableB, List.Zip( {Table.ColumnNames(TableB), Table.ColumnNames(TableA) }) )
in
    Combined

     

    This separates your table into 2 parts TableA and TableB. Then removes rows which are blank.

    Combines the two table and for that a renaming operation is required. With this result.

     

    Ps. If this helps solve your query please mark this post as Solution, thanks!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    thanks, i ended up doing a if col is not null then col, else if col1 is not null col1 else if col2 is not null col2