Find the Most Common Values in a column of Excel (DataTable)

Hi everyone,

After using Read Range to generate a data table from an excel.

I am now in the assign activity, trying to get the most common string value from a column.

How can I achieve this?

Thanks in advance.


Check out this post:

@ppr was very helpful for me :slight_smile:

This is the value I used and what I was getting:


I was expecting an output of the string of the most common value.

find some starter help here. refer to dtStatistic and the log message line:
ColValueCountStatistic.xaml (9.5 KB)

1 Like


Thanks for the quick response!

I tried as below:

However, I received an error saying “Object reference not set to an instance of an object”.

Am I doing sth wrong here?

ensure SpotfireTable is not null. for this you can check the scope of the variable and debug till this assign statement with a breakpoint for detail inspection


Spotfire Table has 41 rows through the display of log message.

However, it’s still showing the same error:

do you have checked if there are null values within the rows and the column CBIg…that you are processing?

Yes, there are some null values in the table, showing as blank in excel cell.

ok, this could cause the issue. just filter these rows out before processing with the LINQ

Do I need to filter out all the rows with null or only the rows with null in my grouping columns? How can I filter the null values out?

can you check before if the result table( I gues BigC_prep… in your case) is correctly declared and initialized as an empty datatable with the two result columns. Also verify the scope

Here’s how it was declared.

in the demo XAML there is build datatable setting up the column structure and initializing the datatatble. This similar part you do need for the BigC… result Table. Just refer to the starter help XAML


This is very helpful. It work finally!
Thank you so much!!

Hi Steven,

Thank you!

This is helpful to me as well. Problem solved.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.