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