Remove Column/s which contain only one specitic value (no other one value is there)

[Kopec]

Hi,

I need to recognize if column of table contains only specific value (if it will contain specific values and other values, do nothing) . In case of TRUE, I need to remove this column/s. I need to do it with many columns contains data type "inteager" and "string" in one table. 

Example:

 Can you help me?

Jan

[AntrikshSharma]

@Kopec What is the logic behind removing those 2 columns?

[Kopec]

Sorry I forgot mention that I can determinate which Value in which Column I seek.

In this example I determinate:

If in column range B:D is Column which has only 0 value, this will be removed.

If in column range E:G is Column which has only "NO" value, this will be removed.

Jan

[ronrsnfld · Accepted solution]

Once in Power Query, you have to make an assumption in order to map the column names to the column letter in Excel.

In the code below I am assuming that the first column of your PQ table is Column B in Excel.  If that is not the case, you will need to edit the code that looks for the zero's and no's to look at the correct columns.

Read the code and the comments to understand the algorithm.

let

//edit next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],

//edit next line to reflect actual column headers
//  or set data types using a dynamic method
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"20220308", Int64.Type}, {"20220309", Int64.Type}, {"20220310", Int64.Type}, {"its possible 1", type text}, {"its possible 2", type text}, {"Its possible3", type text}, {"no remove", type text}}),

//ASSUMES table starts with column B (adjust list ranges if that is not the case)
//  Check for 0's will be columns 1,2,3
//  Check for "NO"'s will be columns 4,5,6
    #"Check for Zero" = List.Range(Table.ColumnNames(#"Changed Type"),0,3),
    #"Check for NO" = List.Range(Table.ColumnNames(#"Changed Type"),3,3),

    #"All Zero" = List.Accumulate(#"Check for Zero",{}, (state, current)=>
        if List.MatchesAll(Table.Column(#"Changed Type",current), each _ = 0) then state & {current} else state),
    #"All NO" = List.Accumulate(#"Check for NO",{}, (state, current)=>
        if List.MatchesAll(Table.Column(#"Changed Type",current), each _ = "NO") then state & {current} else state),

//Delete the relevant columns
    #"Delete Columns" = Table.RemoveColumns(#"Changed Type", #"All Zero" & #"All NO")

in
    #"Delete Columns"

[Kopec]

Thank you, it works great:).

Jan

[AntrikshSharma]

@Kopec Removing column dynamically with PQ is easy however, there is no concept of range in PQ, so how would you deal with that? Once in PQ I can only utilise the column names and the values.