Help With Data Condense

hello arosenberg 

you can do this with PQ or DAX depend on what you need.

PQ : 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUVBWcFTSgXFATAMdUyCpgBXH6uDWp4BHLyF9IGyIX5cTQpcTmkoDHSNTsjSiGmJKsj64xSganREanbFoQvgzNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, #"Employee Name 1" = _t, #"Employee Name 2" = _t, #"Employee Name 3" = _t, #"Employee Name 4" = _t, #"Employee Name 5" = _t, #"Employee Name 6" = _t, #"Employee Name 7" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Employee Name 1", type number}, {"Employee Name 2", type number}, {"Employee Name 3", type number}, {"Employee Name 4", type number}, {"Employee Name 5", type number}, {"Employee Name 6", type number}, {"Employee Name 7", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1", "Column2"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
#"Pivoted Column"

what you do in PQ is basically you unpivot then pivot back. it will automatically remove null value.

DAX: 

Summarize =
SUMMARIZECOLUMNS(
    'Table'[Column1],
    'Table'[Column2],
    "Employee 1",MAX('Table'[Employee Name 1]),
    "Employee 2",MAX('Table'[Employee Name 2]),
    "Employee 3",MAX('Table'[Employee Name 3]),
    "Employee 4",MAX('Table'[Employee Name 4]),
    "Employee 5",MAX('Table'[Employee Name 5]),
    "Employee 6",MAX('Table'[Employee Name 6]),
    "Employee 7",MAX('Table'[Employee Name 7])
)

in DAX, you need to create a new table to summarize original table with looking for value on each column.

Hope this will help.

Thank you.