Forum Discussion

StuartSmith's avatar
StuartSmith
Power Participant
5 years ago

Filter columns if all rows contain data

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 ...
Need Help
CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

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"