how to capture column with NOT NULL value

[summer18]

Hi,

I need help on how to capture columns with non-null values.

Supervisor1 = 
IF EMPLOYEE10 is BLANK then EMPLOYEE9
elseif EMPLOYEE9 is BLANK then EMPLOYEE8
elseif EMPLOYEE8 is BLANK then EMPLOYEE7
elseif EMPLOYEE7 is BLANK then EMPLOYEE6
elseif EMPLOYEE6 is BLANK then EMPLOYEE5
elseif EMPLOYEE5 is BLANK then EMPLOYEE4
elseif EMPLOYEE4 is BLANK then EMPLOYEE3
elseif EMPLOYEE3 is BLANK then EMPLOYEE2

else   EMPLOYEE1
end

formula for Supervisor2... onwards.....

I have tried nested IF and SWITCH statements but could not get it to work.  Below is the link for sample pbix and excel

https://drive.google.com/drive/folders/1WKeAb8NnIeHE931pKDNhPJMjHZuGZFpC?usp=sharing

Regards,

Summer

[v-kelly-msft · Accepted solution]

Hi @summer18 ,

Go to query editor and use below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU9BagMxDPyK8DmXpC9otqWh5FDYkFCWPYhErEVsq8jrgPP6yhtoC4U5zCBpZjQMbu1W7gcHidBHnr3xd/EJXoQaxUSwRZ2N71ERntOFNEtaTlKFLqBel8XzFXb0O+1ruDHCiUNgjNmNq8Ft/iY+0OHsSdlSPqWYfo0cKux54TeEo91jCyexSpHTVOhueqvlTNBzlESL99N/b8Ob0iRa4cBzyaaP9k/OCJ2W5vLhW70vOGGaWhmvnKETperG8Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowID = _t, EMPLOYEE10 = _t, EMPLOYEE9 = _t, EMPLOYEE8 = _t, EMPLOYEE7 = _t, EMPLOYEE6 = _t, EMPLOYEE5 = _t, EMPLOYEE4 = _t, EMPLOYEE3 = _t, EMPLOYEE2 = _t, EMPLOYEE1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RowID", Int64.Type}, {"EMPLOYEE10", type text}, {"EMPLOYEE9", type text}, {"EMPLOYEE8", type text}, {"EMPLOYEE7", type text}, {"EMPLOYEE6", type text}, {"EMPLOYEE5", type text}, {"EMPLOYEE4", type text}, {"EMPLOYEE3", type text}, {"EMPLOYEE2", type text}, {"EMPLOYEE1", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"RowID"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [Value] <> null and [Value] <> ""),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value"),
    #"Renamed Columns" = Table.ColumnNames(#"Pivoted Column"),
    #"Converted to Table" = Table.FromList(#"Renamed Columns", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each [Column1]),
    List = Table.ToRows( Table.ReplaceValue(#"Added Custom","EMPLOYEE","Supervisor",Replacer.ReplaceText,{"Custom"})),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",List)
in
    #"Renamed Columns1"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[summer18]

Thank you @v-kelly-msft, it works

[v-kelly-msft]

Hi @summer18 ，

Glad to help.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[amitchandak]

@summer18 , a new column like

Supervisor1 =
Switch(True(),
isblank([EMPLOYEE10]), [EMPLOYEE9],
isblank([EMPLOYEE9]), [EMPLOYEE8],
isblank([EMPLOYEE8]), [EMPLOYEE7],
// add others
[EMPLOYEE1]
)