Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 @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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
41 | |
23 | |
21 | |
21 | |
13 |