Need to Get Table.ColumnCount if column contains specific value

[krigovi]

Hello Team,

I have 10 Excel sheets, where i am trying to get column count if Column contains value "Different".

I have tried few Formaulas In power Query but nothing worked 

Table.ColumnCount( Table.SelectRows([Sheets],each Text.Contains([Column Name],"DIFFERENT")))

I have created column with all Column name using formula : Table.ColumnNames([Sheets]),

and sheets is my table 

can some one help me to get the Query right ?

[rohit1991]

hi @krigovi , Use this concise formula in Power Query:

List.Count(List.Select(Table.ColumnNames([Sheets]), (col) => List.Contains(Table.Column([Sheets], col), "Different")))

[krigovi]

Hello @rohit1991 

Thankyou for your Insight.

I like to get the count of column which contains "Different" in the row, not in the Column Name

Here i have Value Different in the row so i lkike to count that column

[danextian]

Hi @krigovi 

Please try this as a custom column:

let 
//creates a list containing the header names of a table
ColumnNames = Table.ColumnNames([Sheets]),
//check if any of the list items contain the word DIFFERENT, case-sensitive
ContainsAWord = List.Select(ColumnNames, each Text.Contains(_, "DIFFERENT")),
//count the items in the filtered list
ListCount = List.Count(ContainsAWord)
in
ListCount

[rajendraongole1]

Hi @krigovi  - Use Table.SelectRows to filter rows in each sheet where any column contains "Different".

This will create a new column, "Count Columns with 'Different'," that contains the count of columns with the value "DIFFERENT" for each table in your dataset.

attached mcode FYR

[krigovi]

Hello @rajendraongole1 

Thankyou for your Valuable Solution,

I tried it and it provides Value 0. 

I need Count of column which contains value "DIFFERENT"  can you share little more insight ?

[rajendraongole1]

Hi @krigovi - can you please check below code use the following formula in the Custom Column editor.

[bhanu_gautam]

@krigovi , Try using 

In Power Query, go to Home > Advanced Editor.
Create a new function to check if a column contains the value "Different".

let
CheckColumnForValue = (table as table, value as text) as logical =>
let
columns = Table.ColumnNames(table),
containsValue = List.AnyTrue(List.Transform(columns, each List.Contains(Table.Column(table, _), value)))
in
containsValue
in
CheckColumnForValue

Go back to your main query where you have all the sheets.
Add a custom column to apply the function to each sheet.

let
Source = Excel.Workbook(File.Contents("YourFilePath.xlsx"), null, true),
Sheets = Source{[Item="Sheet1",Kind="Sheet"]}[Data], // Repeat this for each sheet
AddCustom = Table.AddColumn(Sheets, "ContainsDifferent", each CheckColumnForValue([Data], "Different")),
Filtered = Table.SelectRows(AddCustom, each [ContainsDifferent] = true),
ColumnCount = Table.RowCount(Filtered)
in
ColumnCount

[shafiz_p]

Hi @krigovi  Try this line of M code : 

AddCountColumn = Table.AddColumn(Source, "DifferentCount", each List.Count(List.Select([Column Name], each Text.Contains(_, "Different"))))

Change 'Source' with the last step name. Where Column name representing list with column name of those tables. If not, then try this code to create a column name list column:

AddColumnNamesList = Table.AddColumn(#"Filtered Rows", "ColumnNamesList", each Table.ColumnNames([Sheets])),

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[krigovi]

Hello @shafiz_p 

Thankyou for your valuable Solution,

Unfortunately i am getting 0 As result.

[shafiz_p]

If your list contains table column names and your column name contains "DIFFERENT", then this formula should work. You see that it return 0, that means formula working but not finding any word "DIFFERENT". Please check each list to make sure, column name exist with the desired word. Also, check header as @danextian  suggest.

Hope this helps!!

[krigovi]

Hello @shafiz_p 

Thankyou for your Feedabck,

Yes I can understand that, I need Count of Column which contains "DIFFERENT" in the row, not in Column name

Your Insight would be verymuch Appriciated...

[shafiz_p]

Hi @krigovi  You have provided wrong information and desired output. However, to count rows which contain specific word, try below steps:

AddCountColumn = Table.AddColumn(PreviousSteps, "DifferentCount", each try if Table.HasColumns([Sheets], "Column Name") then Table.RowCount(Table.SelectRows([Sheets], each Text.Contains([Column Name], "Different"))) else 0 otherwise 0)

Change column name accordingly.

[Sheets] column will contain your table and the column name from which you want to count rows which contains specific word, for your case "DIFFERENT".

For my case, I have used Customer Name column from table. Each table contain this column, if not then will return 0.

Hope this will solve your problem!!

[danextian]

Hi @krigovi 

Have you checked if Column Name column actually contains the word you're looking for. I am thinking, you used Table.ColumnNames on tables without promoting the first row as the headers first - if this is the case, the column names will be just Column1, Column2, etc.  Please ensure they're promoted first.

Table.ColumnNames(Table.PromoteHeaders([Data Column], [PromoteAllScalars = true]))