cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kopec
Helper I
Helper I

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

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:

2022-11-18_18-28-32.png

 Can you help me?

Jan

1 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"

 

ronrsnfld_1-1668865888296.png

 

 

 

 

 

View solution in original post

5 REPLIES 5
AntrikshSharma
Community Champion
Community Champion

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

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

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"

 

ronrsnfld_1-1668865888296.png

 

 

 

 

 

Thank you, it works great:).

Jan

@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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors