Search code examples
nullfindrangepowerquery

Powerquery list column names of all blank / null columns on each row

Pls guide me to extract null cells if available from selected columns. this will help me to find null cell available in cell of selected columns... i have tried to get this data by if function but there are so many conditions needs to be put in if function. i need to this information by reducing the formula conditions if it is possible to get then pls support me.

enter image description here

Solution

  • This will add a column that shows all the names of columns in that row that are null. Is that what you are looking for?

    let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each 
Text.Combine(
    List.Transform(
        List.PositionOf(
            Record.ToList(_)
        ,null,999)
    , each Table.ColumnNames(Source){_}),
 ", ")
)
in #"Added Custom"

    or more compactly

    let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.Transform(List.PositionOf(Record.ToList(_),null,999), each Table.ColumnNames(Source){_}),", "))
in #"Added Custom"

    enter image description here