SharePoint List Input Changing Number Columns with Percentage to 1, 0, or Null

[marianawith1n]

Hi, everybody,

I'm having a problem with some SharePoint columns that have been set up as number (percentage.) When using the SharePoint List Input in Alteryx, I lose the values. They all come in as 1, 0, or Null. If I were to export the list to Excel and then use the resulting file as my input for Alteryx, then I have actual numbers I can work this. But this is not what I need for this particular solution. I need it to be inputting data directly from my SharePoint list. I'm including an image (see below) with screenshots from both SharePoint and Alteryx. This particular SharePoint list I need for my Alteryx input has about 20 columns set up as Number (show as percentage.) I have done some research in and out of this community, and I've seen suggestions to change the column to number and not use percentage, add decimals, and many other things. I cannot do that for this particular list. There are many dependencies and modifying the columns will affect the content in other columns and the final numbers we need will be off.

Does anybody know how to fix this? Thank you so much in advance!

[rafalolbert]

Hi @marianawith1n,

I just had a look using the SharePoint List Input, as well as a Python script with direct API, which i'm currently working on and in both the % values are fine. 

I think your problem is the Select tool with Fixed Decimal 18.0 value - this is changing your numbers - see below for reference:

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[marianawith1n]

Thank you for taking the time to send something my way.

Before I even posted in here, I tried changing it at the Select tool level. It did not work. So I started to look at the outputs / results each step of the process. That's why I think the problem is before it reaches the Select tool. See image below. I made sure to select SharePoint List Input. Then I checked the results. As you can see, they are already showing 1's and Nulls before it reaches the Select Tool. Changing it to other than 18.0 or something else in the Select tool does nothing for me because the input for the Select tool is already 1's, 0's, and Nulls. 

Now, if I were to export the very same list to Excel and then input the resulting file, then the values coming in are correct, as you can see in the image below. Unfortunately, having to manually export the list from SharePoint to Excel so it can then go into Alteryx is not efficient or practical for this particular solution I'm after. I need it to be able to connect to SharePoint directly.

I'm not that advanced in Alteryx. But I have over 15 years experience with SharePoint. So I can try different things from SharePoint. But at this point, I'm starting to think is the way these columns are imported in Alteryx or the way Alteryx understands that particular type of column and yes, I may need to change a setting or something, which I do not know which that would be. But changing things at the Select tool level has not worked for me. That's why I thought asking around.

Any other ideas?

[rafalolbert]

Hi @marianawith1n,

Have you checked some other integrations using APIs?

I'm a big fan of Alteryx + SharePoint integrations, but have come across multiple limitations like 5K record limit for getting data out, no support for libraries in Alteryx etc. and decided to develop SharePoint connectivity from ground up, here are some posts with a few pointers.

Where this is not the most straightforward to start with, there are many benefits of using direct API access - i'm hoping this may eliminate your problem as well:

• https://community.alteryx.com/t5/Alteryx-Designer-Discussions/SharePoint-List-Input-limitations/m-p/...
•  
• https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-download-a-Excel-file-from-Shar...

Thanks,

Rafal

[marianawith1n]

Unfortunately, I cannot do that because of how things are implemented at this company. But thank you for the suggestion.

[marianawith1n · Accepted solution]

I was able to fix it but within SharePoint. I created calculated value columns that reference the columns with the percentage values, set the data type to be returned as number with an automatic number of decimal places. I modified my view to include the calculated value columns instead of the columns filled by the user. Problem solved.

Thank you!