how to capture column with NOT NULL value

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!