Sharepoint Choice Column text value into Excel

[Sheen]

Hi Folks,

Any help on this would be appreciated. I have a Sharepoint list that I am looking to extract the rows from to create an Excel table, however, I have a few complex columns and when I do this I get a bunch of text I don't want in to Excel data. How do I get only the value that has been selected ?

Regards

Sherinne

[creativeopinion]

@Sheen You might find this YT Tutorial helpful: ✌️TWO WAYS to Get Data from Excel into a SharePoint List

There are two ways to get your data from Excel into a SharePoint List:

1️⃣ Copy and paste

2️⃣ Automatically with a Power Automate flow

In this video tutorial I will show you how to get your Excel data into a SharePoint List using the two methods listed above. I’ll cover how to format your data for complex columns such as a multi-choice and multi-person columns.

---

IN THIS VIDEO:

✅ How to copy and paste data from Excel into a SharePoint List

✅ How to format Excel data for multi-choice columns in a SharePoint List

✅ How to import data from Excel into a SharePoint List with a Power Automate Flow

✅ How to use the Select action in Power Automate

✅ How to use the Create Item action in Power Automate

✅ Enabling the Concurrency Control to Optimize Flow Performance

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[Sheen]

Hi Creativeopinion, thanks for this but I have the data in sharepoint and need to export it to Excel - this seems to work on moving data from excel into SP

[creativeopinion]

@Sheen Sorry about that. Misread that. Can you specify what complex columns you are working with and what format you want to output for Excel?

[Sheen]

No Problem, I have a choice column (which accepts multiple selections but may also only include one) and I have a date column

[creativeopinion]

 I'm not sure what your flow looks like.. but I would recommend using a Manually Triggered Flow so you can quickly trigger tests while you build your flow. Get Items

It's recommended to use a Filter Query to reduce the number of items returned form your SP List. You will need to use the internal column name of your columns. Keep in mind that the internal column name may not always match the name displayed in your Sharepoint list. If you aren’t sure how to get this, please refer to this section of one of my YT tutorials.

Also, depending on how many items you have in your SP List (even if the list is filtered), I would recommend limiting the Top Count to a smaller number than the total number of items in your list. This will increase the speed of your flow runs. Instead of returning all items in your list—it'll limit the Get Items action to the number entered into the Top Count field.

Return Item Count (Optional)

Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow. For more flow troubleshooting tips—check out this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this tutorial I cover:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅ How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

Run a test. Note: If you've specified a Top Count as I've done above—the number returned should be the same. 

Apply to Each

Add an Apply to Each action to loop through each item returned from the Get Items action. I would recommend using Compose actions to compose the dynamic data first and run tests to ensure the outputs of your Compose actions are what you are expecting. Remember to rename your actions to keep things organized. It will also help to easily identify which dynamic content belongs to which action. 

You can then insert those outputs into the Add a Row into a Table action. 

Date: If you want to format your date from SharePoint into a specific format, use the formatDateTime() function. You'll need to use an expression to get the dynamic content of your date column which I cover in this section of my YT Tutorial.

Add a comma and single quotes and insert the date format. 

Run a test. Review the outputs of the Compose action—adjust as necessary.

Multi-Choice: To convert your multi-choice options from an array to a string, you'll need to select the values first with a Select action. In the from field, you'll need to insert the dynamic content of the multi-choice column. 

Click on this icon to switch to text mode. 

Insert the value dynamic content from the multi-choice column. When you do this, Power Automate will automatically nest your Select action inside an apply to each action which isn't necessary.

Drag your select action outside of the automatically added Apply to Each action and delete it.

Run a test. Review the outputs of your Select action. 

Now you need to convert the array of values into a string. Use a Join action. In the From field, insert the output of the Select action. In the Join with field, insert a separator. In my case I'm using a comma and a space—use whatever separator suits your needs.

Run a test. Review the outputs.

Do this for any complex columns you have. For simple columns you can simply insert the dynamic content from the Get Items action into the Add a row into a table action. 

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

@Sheen