How do you append multiple columns into one column?

[Jojojojo]

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

[AlexisOlson · Accepted solution]

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"]

[Jojojojo]

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")

[wdx223_Daniel]

=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}?)