Forum Discussion
The dreaded Apostrophe is turning numbers into text in a forms spreadsheet
I guess the Form is linked to Excel file hosted on SharePoint site. That issue is known for years, at least two workarounds
1) If you have column [Question 1] which have number as text, you may add to the table additional column Q1 as =@[Question 1]*1, or =CLEAN(@[Question 1])*1, etc and work with these columns. Adding of additional columns into the forms table won't crash the process.
2) Instead of linked table use Power Automate which is triggered by form submit and adds rows to the table in your file. In this case not linked with the Form. Transformation could be done within Power Automate flow.
I guess the Form is linked to Excel file hosted on SharePoint site. That issue is known for years, at least two workarounds
1) If you have column [Question 1] which have number as text, you may add to the table additional column Q1 as =@[Question 1]*1, or =CLEAN(@[Question 1])*1, etc and work with these columns. Adding of additional columns into the forms table won't crash the process.
2) Instead of linked table use Power Automate which is triggered by form submit and adds rows to the table in your file. In this case not linked with the Form. Transformation could be done within Power Automate flow.
- As this has been bumped, I'll just add that whenever I do this now, I tend to just use Power Query instead of Power Automate. you can PQ in the same form workbook to a new sheet. Keeps raw data safe and solves this issue.
I'd Power Query into another workbook and keep updated by Forms one untouched. If you use data model in that workbook you may publish it on Power BI and schedule refresh, but it depends how often it shall be updated. One more option is email reports (mainly tables) from this workbook using another Power Automate flow.
- Thanks. Re 1. That's good to know re extra columns, I've added columns at the end on form response sheets, but haven't been game to add columns in the middle.
2. That is the work around I ended up with this time. It's worked well, as with numbers in place, everything flows pretty easily from that. Far less reliance on formulas for what we need now, just a couple of pivots and some sheets referencing other sheets and splicers to allow folks to glance at what they want.It's always better to split Excel workbook on 3 zones
- source/raw data
- data preparation
- reporting
First is filled automatically by SharePoint app triggered by Forms
- in second you may generate any table and perform any calculation to transform data
- in third could be PivotTables / Charts based on second part.
