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:
- Open your SharePoint list.
- Click on the column header to sort it.
- After sorting, look at the top of the column; the display name is shown there.
- Hover over the column header, and you might see the system name appear at the bottom of your browser window.
Method 2: List Settings
Another effective way to find system names is through the list settings:
- Click on the settings gear icon in the top right corner.
- Select “List settings.”
- In the settings menu, you’ll see a list of all columns.
- Hover over each column name to view its system name in the URL displayed at the bottom of your browser.
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.”
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.
Step 5: Transform the Data
Once the data is loaded, you may need to transform it to display the relevant columns:
- Click on “Transform Data” to open the Power Query editor.
- Look for columns labeled “Static Name” and “Title” which correspond to the system name and display name respectively.
- Sort or filter the data as needed.
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!