Answered

Count distinct values in SharePoint list and send HTML table of the values with its count in a table format

(0) Share

Report

Posted on by jophi1124

I have a SharePoint list and contains multiple items with some single and some multiple values per schedule. After each scheduled flow, all the SP items will be swept and I want to count each distinct entry in the Title column and each count should be sent in a table sent to email. I can't find a way to filter the result with its count and combine it in one table. below is the expected result

Title Name (alphabetized)	Count
Title1	1
Title2	3
Title3	5
Title4	2
Title5	1

Categories:

General topics

All responses (9)

Answers (2)

Sort by

Verified answer

v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @jophi1124 ,

Please follow below steps:

The result HTML table can be used in Email body.

Best regards,
jophi1124 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for the response. May I ask a few more nuances?
1. I failed the Get items 2--one of the possible title contains apostrophe in the name so it got an error in the formula Title eq '@{items('Apply_to_each')?['Title']'. Would you know how to get around it?
2. Can you let me know what is the output 2 on the From on Create HTML table? I do not seem to figure it out from the pictures.
wskinnermctc 6,519 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

This sounds harsh, but you should change the title name in the SharePoint list so that it doesn't have an apostrophe. It will help you in the long run.

https://learn.microsoft.com/en-us/microsoft-365/community/sharepoint-naming-guidelines

After you have to deal with residual problems enough you'll learn to fix column names up front. Give them an initial name that doesn't have any special characters so that it will be stored internally like that. Then go back and change the name after the column is made and the display name will be different but the internal name will always be the same.
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @jophi1124 ,

Please find internal column names in the URL of the page that editing a column in list settings, then use them for filter query. For variable 2, it is actually variable 1, output from append to array variable in Apply to each. I edited names of the two variables but forgot to change it in the last action.

Best regards,
jophi1124 21 on at

Like
a
(0)

Report
Copy link

Link copied!

v-jefferni, sorry for failing to make it clearer. the error is below:

one of the entries under title column is:
AMERICA'S.... that's why the flow stopped during Get items 2. Is there a work around for that if there's apostrophe on the SahrePoint entry for cloumn Title (or any column from sharepoint with text format that may contain ' ? other example we may get are ST. JOHN'S... etc.

It's not the Title column header that has the apostrophe.
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @jophi1124 ,

Column values will not cause that error.

You can find the real column names from the outputs of the first Get items action in flow run history:

You can press CTRL+F keys to search body in the page, and find the internal column name then copy it to filter query.

Best regards,
jophi1124 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, I believe my issue is about this: https://powerusers.microsoft.com/t5/Building-Flows/Data-contains-an-apostrophe-and-gets-skipped/td-p/617219

This is my raw data:
I ran your flow again and got this error:
Verified answer

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @jophi1124 ,

So, have you tried below expression in filter query?

replace(items('Apply to each')?['Title'],'''','''''')

BR,
jophi1124 21 on at

Like
a
(0)

Report
Copy link

Link copied!

yes it worked now. Thank you sooo much!

Raw data: 4

Result: 4