Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mts
Frequent Visitor

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

1 ACCEPTED SOLUTION
mts
Frequent Visitor

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

View solution in original post

2 REPLIES 2
mts
Frequent Visitor

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

m_dekorte
Super User
Super User

Hi @mts,

 

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

m_dekorte_0-1684760644710.png

 

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.

m_dekorte_1-1684760673028.png

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors