Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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...
Need Help
Tutorial Requests
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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!!

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Anonymous , 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