Filter columns if all rows contain data

Hi StuartSmith 

You can do this best in Power Query. Place the following M code in a blank query to see the steps of an example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMgTjWJ1oJSMgC4iMwBxjIAuEwRwTIAOCY2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "isComplete", each not List.Contains(Record.ToList(_), null)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([isComplete] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"isComplete"})
in
    #"Removed Columns"

 The code add a custom column that check if any of the columns if that row is empty (null). It then filters out the rows that are complete. Note that you might have to tweak the code a bit if instead of null you are looking for "" for text columns

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

Hi, StuartSmith , as to me, Power Query might be more competent in the such a data cleansing job. Pls refer to the code below,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck8tyk3Mq1TSUXJKLcrJzAMyDIEYimJ1opU8i1JzEvNSgFyX0iSsKoILEsHCvolFKUWZKVAFMAxWEuoNZPnk56Xk56FJG0Lkg4HM8MTijMy89BJsamIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, City = _t, Floors = _t, Users = _t, Other = _t, Desk = _t]),

    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Replaced Value" = Table.ReplaceValue(#"Demoted Headers","",null,Replacer.ReplaceValue,Table.ColumnNames(#"Demoted Headers")),
    Cols = Table.ToColumns(#"Replaced Value"),

    //Number of columns as headers
    #"Cols Header" = 2,

    #"Cols Kept" = List.FirstN(Cols,#"Cols Header") & List.Accumulate({#"Cols Header"..List.Count(Cols)-1}, {}, (s,c) => if List.Count(Cols{c})<>List.NonNullCount(Cols{c}) then s&{Cols{c}} else s),
    #"Table Shown" = Table.PromoteHeaders(Table.FromColumns(#"Cols Kept"))
in
    #"Table Shown"

Hi StuartSmith,

In fact, dax formula not suitable for these options. If statement also not able to handle table-level results, I'd like to suggest you refer to AlB suggestion to use power query functions to do achieve your requirement.

Regards,

Xiaoxin Sheng