Forum Discussion

kosv's avatar
kosv
Regular Visitor
1 year ago
Solved

Different Column Names when importing Sharepoint List via OData

I am importing data from a local Sharepoint List with the OData connector in PowerBI Desktop. I receive the column names like 'ID', 'Modified Date' etc and transform the data to change names, remove ...
Need Help
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    Hi kosv  ,This issue is typically caused by regional settings and localization differences in Power BI Desktop or the underlying SharePoint environment. Even if you both use the same Power BI Desktop version, differences in regional settings (either in Power BI, Windows, or SharePoint) can result in column names being localized when connecting via OData.

    How to Fix This:

    1. Disable Column Renaming Based on Locale in Power Query:

    • In Power Query Editor, go to File > Options and settings > Query Options.
    • Under Current File > Regional Settings, ensure that the locale is set consistently for both machines.
    • Check the box "Always allow data previews to download in the background" and ensure that the column renaming option is consistent.

    Also, look for a setting called "Never rename columns based on locale" (available in the OData feed options). Enable it to prevent automatic column renaming during OData import.

     

    2. Manually Force Column Names in Power Query:

    In Power Query Editor, use the Table.RenameColumns function to explicitly rename each column after the data is imported, regardless of the locale. For example:

    Table.RenameColumns(Source, {{"Id", "ID"}, {"ModifiedDate", "Modified Date"}})

    This ensures that no matter the locale, the column names will always be consistent.

     

    3. Ensure Regional Settings Match:

    • Verify that both you and your colleague have the same regional settings in:
    • Power BI Desktop (Options > Regional Settings).
    • Windows OS Regional Settings.

    SharePoint Language Settings (in SharePoint, check under Site Settings > Regional Settings).

     

    4. Recreate OData Connection with Locale Option:

    Recreate the OData connection with an explicit Accept-Language header in the OData query if possible.

    In Advanced Options while connecting via OData, add the following line to ensure English is enforced:

    Accept-Language: en-US

     

    5. Avoid Using Auto-Detect Column Types:

    In Power Query, disable auto-detect column types and headers if necessary. This avoids unintended changes during refresh.

     

    Please mark this post as solution if it helps you. Appreciate Kudos.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi kosv ,

Agree with FarhanJeelani's reply!
And kosv , I would like to add a possible solution:
In addition to the display name, a column in a SharePoint list also has an internal name that does not change with the language or regional settings, so you can use the internal name in Power Query to avoid localization issues.
https://learn.microsoft.com/en-us/microsoft-365/community/sharepoint-naming-guidelines#internal-names 
https://blog.softartisans.com/2009/12/08/list-of-internal-names-for-sharepoint-fields 

You can find the internal name of a column in a SharePoint list by going into the list settings, or by using the SharePoint REST API to get the details of a column, which includes the internal name:
https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest 

In the Power Query Editor, replace the column names with the internal names. For example, change 

#"Renamed Columns" = Table.RenameColumns(Source, {{"ID", "NewID"}})

into

#"Renamed Columns" = Table.RenameColumns(Source, {{"_ID", "NewID"}})

where _ID is the internal name of the column.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.