Filter columns if all rows contain data

[StuartSmith]

I am creating a report to show all records that have missing data, so that the dataset owner can update the missing data.

I currently have a table that shows rows that have missing data and as a row is completed, the row is filtered, so overtime, the number of rows will reduce to eventually zero as all rows would contain data.

Is it possible to filter columns, so once a column has no empty cells, it doesnt show?   Hope that makes sense.

[Anonymous]

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

[StuartSmith]

Sorry, I thought I replied to this thread.  I tried the solution by "CNENFRNL", but found that it didnt hide the columns dynmically.  What I mean by this, is that when the created the power query solution, it hide any columns that already had data in every row, but if I updated the data source and then refreshed the data, any new columns that had all row completed were still visible and were not removed.

[Anonymous]

Hi @StuartSmith,

In fact, power query table structure will be cached in query steps and they weren't dynamic changes if the result changes.  You need to manually manage these structures and fix them in the following steps if the raw calculation results changed.
Regards,

Xiaoxin Sheng

[StuartSmith]

Thanks, I will just tell the user that its currently not possible.  

[CNENFRNL]

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"

[AlB]

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