When I import a SharePoint list from an Excel table, the column headers all get changed to "field_#", where # is the column number. The table contains a first row of column headers. Previously I have imported similar tables into SharePoint and the absolute column references were "field_ColumnName", where ColumnName is the actual column header from the Excel table.
Well I had to makes some changes to get the column headers of several tables (each table in its own worksheet) to match each other. For instance, I need them all to have either "State" or "Province" but not a mix of both. Or I need the postal code columns to all be "Zip Code", instead of some being "Postal Code" and others being "Zip", etc. etc.
I just need column headers to match. Each table has a different set of headers, but they share many of the same column categories. I just need the corresponding headers to match exactly. So I have started editing these tables to fix the mismatched column headers, but when I delete the old version of the SharePoint list and import the revised table into a new list (with the same exact list name), the absolute column references are no longer coming in as "field_ColumnName" like they used to. Now they're all "field_#". This is a huge problem for me!
The data from these lists needs to populate galleries and forms within a Power Apps canvas app, and we use Power Automate flows to query SharePoint for the list data and return it to the canvas app (we call a parent flow from the canvas app which calls a child flow to query SharePoint, thus ensuring we're always in the creator's context rather than user's context. We don't directly query SharePoint lists from within the canvas app because then each user would be required to have their own SharePoint connection...).
Working with columns that get renamed from "ColumnName" to "field_ColumnName" was annoying, but we learned to live with it because it's intuitive enough to type a column name into some Power Apps function and look for the corresponding auto-suggested "field_ColumnName". That's slightly confusing and annoying but really no big deal. It gets a whole lot more confusing if we have to translate back and forth from descriptive column names to arbitrary numbers!
How can we prevent SharePoint from changing our descriptive .xlsx table column headers to arbitrary numbers upon import? This is ridiculous! So maddeningly frustrating.
Does this have anything to do with having previously imported (and then deleted) Excel tables with very nearly the same set of column headers and exactly the same list names? The original lists were deleted (sent to SharePoint recycling bin). The Excel tables were revised to fix the mismatched column headers between corresponding fields in each table. Then the Excel tables were imported into brand new SharePoint lists with the exact same list names as the original lists that we deleted. Most of the column headers in each new list are exactly the same as the old list, with only a couple of them being different from that original list. But every single column now gets an absolute reference that's just a number rather than the column name. I think we've deleted and reimported lists before without seeing this issue. Not entirely sure what's different this time🤷
Edit: Just tried importing an Excel table as a brand new SharePoint list and left the auto-generated random string appended to the list name (thinking maybe some conflict arose due to reusing the exact same list name as the deleted list), but nope! Changing list name doesn't have any effect.
Edit2: Restored one of the original lists from Recycle Bin so that we could open it for comparison, then imported the exact same Excel table that was used to make the original list (no changes whatsoever to that data or column headers) into a brand new list. To avoid conflict between the duplicate list names, I allowed SharePoint to append a random string to the list name, whereas usually we delete that random string. We thought using the unedited Excel table would result in the old "field_ColumnName" style of absolute column reference that we prefer, but to my surprise we got "field_#" instead. So it seems that the changes to the Excel file itself have absolutely nothing to do with the issue because the issue occurs with the exact same Excel file which previously worked. There may have been some global SharePoint settings changed since the last time we built a list via Excel import. Could it be that some SharePoint setting is causing this?
Edit 3: Ooo after perusing the SharePoint site settings, I think maybe "site columns" could have something to do with this...? There is some overlap between columns that I see in the "site columns" list and the columns which I'm trying to update with matching headers for corresponding columns in several lists. I think I need to make all of the shared fields, as in the fields which several tables have in common but whose headers don't match perfectly, into site columns and use the site column for each of them instead of what SharePoint generates automatically, but I don't know how to make that work with the "Import from Excel file" list creation method. I were manually adding each column, I could add them as site columns or use existing site columns, but importing an entire table full of data doesn't present any opportunity to specify site columns... I think I'm more confused now than I was before!
Final Edit: Aha! This solved my problem, found from this comment.
