Forum Discussion
Need to Get Table.ColumnCount if column contains specific value
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.ColumnC...
Hi Anonymous 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
Super User
Super UserHi Anonymous
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