Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
Hi @mts,
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!