I have this written in DAX but I actually have to write it in Power Query. I need a column that adds a 1 if the other columns do not equal “NA” and then adds the 1s together. So if column 1, 2, and 3 were yes and column 4 and 5 were NA then the new column would be the number three.
1. new column =
IF(table[column_1)]<>"NA",1,0)+
IF(table[column_2]<>"NA",1,0)+
IF(table[column_3)]<>"NA",1,0)+
IF(table[column_4)]<>"NA",1,0)+
IF(table[column_5)]<>"NA",1,0)
I have tried this but am getting an error:
= Table.AddColumn(#"Added Custom1", "Addition", each List.Sum({each if [#"column 1"]<>"NA" then 1 else 0}, {each if [#"column 2"]<>"NA" then 1 else 0}, type number))
If adding a column using GUI:
= List.Count(List.FirstN(List.Transform(
{[column_1], [column_2], [column_3], [column_4], [column_5]},
each if _ <> "NA" then 1 else null),
each _ <> null))
Or if editing the code directly:
#"Added Custom" =
Table.AddColumn(#"PreviousStepName",
"Custom",
each List.Count(List.FirstN(List.Transform(
{[column_1], [column_2], [column_3], [column_4], [column_5]},
each if _ <> "NA" then 1 else null),
each _ <> null)))