Forum Discussion
How do you append multiple columns into one column?
Hi there,
I'm pretty new to Power Query and am looking to append 5 diffrent columns into one.
From my formula below, the new column only seems to populate data from the columns ([Initiative Description] and [#"MAT - Initiative Description"].
While the other 3 columns [#"ENG - Initiative description"], [#"WAT -Initiative description"] & [#"OTH - Initiative description"] are coming out as null's.
= Table.AddColumn(#"Added Custom3", "Initiative Description All", each if [Initiative Description] = null then [#"MAT - Initiative Description"]
else if [Initiative Description] = null and [#"MAT - Initiative Description"] = null then [#"ENG - Initiative description"]
else if [Initiative Description] = null and [#"MAT - Initiative Description"] = null and [#"ENG - Initiative description"] = null then [#"WAT -Initiative description"] else if [Initiative Description] = null and [#"MAT - Initiative Description"] = null and [#"ENG - Initiative description"] = null and [#"WAT -Initiative description"] = null then [#"OTH - Initiative description"] else [Initiative Description])
Hope someone can help out,
Thanks,
Jo
If I'm understanding correctly, this seems like a good use case for the coalesce operator.
[Initiative Description] ?? [#"MAT - Initiative Description"] ?? [#"ENG - Initiative description"] ?? [#"WAT -Initiative description"] ?? [#"OTH - Initiative description"]
3 Replies
If I'm understanding correctly, this seems like a good use case for the coalesce operator.
[Initiative Description] ?? [#"MAT - Initiative Description"] ?? [#"ENG - Initiative description"] ?? [#"WAT -Initiative description"] ?? [#"OTH - Initiative description"]Thanks this worked for me! Followed what was on the post and wrote it out like:
= Table.AddColumn(#"Added Custom3", "Initiative Description All", each
if [Initiative Description] <> null then [Initiative Description]
else if [#"MAT - Initiative Description"] <> null then [#"MAT - Initiative Description"]
else if [#"ENG - Initiative description"] <> null then [#"ENG - Initiative description"]else if [#"WAT -Initiative description"] <> null then [#"WAT -Initiative description"]
else if [#"WAT -Initiative description"] <> null then [#"WAT -Initiative description"]
else if [#"OTH - Initiative description"] <> null then [#"OTH - Initiative description"] else "N/A")
=let col=List.Select(Table.ColumnNames(#"Added Custom3"),each Text.EndsWith(_,"Initiative Description",Comparer.OrdinalIgnoreCase)) in Table.AddColumn(#"Added Custom3","Initiative Descriptin All",each List.RemoveNulls(Record.ToList(Record.SelectFields(_,col))){0}?)
