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.

Hello

Check out this post:

@ppr was very helpful for me :slight_smile:

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


image

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

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

1 Like

Hi,

Thanks for the quick response!

I tried as below:
image

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

Hi,

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

However, it’s still showing the same error:
image
image

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

Hi,

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.