Extracting SharePoint System and Display Column Names with OData Query

When it comes to working with SharePoint, understanding the difference between display names and system names for columns is crucial. This guide walks you through the process of extracting both the system names and display names using OData queries in Power Query.

Why Knowing the System Name Matters

Many users are familiar with the display names of SharePoint columns, which are user-friendly and easy to remember. However, behind these display names lie the system names, which are essential for data manipulation and integration tasks. Think of it like knowing the scientific name of a species; it’s not just for academics, but it’s vital for clear identification in technical contexts.

Finding Column Names in SharePoint

There are various methods to discover the system names of columns in SharePoint. Let’s explore a couple of straightforward approaches.

Method 1: Sorting Columns

One of the quickest ways to find out the system name of a column is to sort it within the SharePoint list. Here’s how:

  1. Open your SharePoint list.
  2. Click on the column header to sort it.
  3. After sorting, look at the top of the column; the display name is shown there.
  4. Hover over the column header, and you might see the system name appear at the bottom of your browser window.

Sorting columns in SharePoint to find system names

Method 2: List Settings

Another effective way to find system names is through the list settings:

  1. Click on the settings gear icon in the top right corner.
  2. Select “List settings.”
  3. In the settings menu, you’ll see a list of all columns.
  4. Hover over each column name to view its system name in the URL displayed at the bottom of your browser.

Accessing list settings in SharePoint

Extracting System and Display Names Using OData

For users dealing with many columns, manually checking each one can be tedious. Instead, you can leverage OData to extract all column names efficiently. Here’s a step-by-step guide:

Step 1: Prepare Your SharePoint Site URL

To begin, you’ll need the URL of your SharePoint site. Make sure you have access to it.

Step 2: Open Excel and Get Data

1. Open Excel.

2. Go to the “Data” tab and select “Get Data.”

3. Choose “From Other Sources” and then “From OData Feed.”

Getting data from OData feed in Excel

Step 3: Enter Your OData Feed URL

Paste your OData URL into the dialog box. The URL should look something like this:

https:///_api/web/Lists/GetByTitle('')/Fields

Ensure that you replace and with your actual site and list names.

Step 4: Authentication

If prompted, enter your authentication details to access the SharePoint data.

Authentication prompt in Excel for OData feed

Step 5: Transform the Data

Once the data is loaded, you may need to transform it to display the relevant columns:

  1. Click on “Transform Data” to open the Power Query editor.
  2. Look for columns labeled “Static Name” and “Title” which correspond to the system name and display name respectively.
  3. Sort or filter the data as needed.

Transforming data in Power Query

Step 6: Load the Data Back to Excel

Once you have the necessary columns filtered, close the Power Query editor and load the data back into Excel. You’ll now have a complete list of both the display names and system names of your SharePoint columns.

Conclusion

Using OData to extract system names and display names from SharePoint can significantly streamline your workflow, especially when dealing with large lists. Understanding these names is vital for accurate data manipulation and integration tasks. If you have any questions or need further assistance, feel free to reach out!

You May Have Missed