**Our Take**
This user's question gets straight to a real limitation in how most people work with SharePoint choice columns. They want a clean, separate reference list of all possible status values, New, In Progress, Completed, without having to rely on the data that happens to be entered in the project list. That is not a niche request. It is a fundamental need for anyone building maintainable reports, validation rules, or dropdowns that stay accurate over time. And the fact that Power Query, for all its power, does not offer a direct "get me the choice definitions" function from SharePoint is a genuine frustration.
The problem is structural. When you pull a SharePoint list into Power Query, you only get the records that exist. If your T_PROJECT list currently contains only one project with a status of "New," then "In Progress" and "Completed" simply do not appear in your query output. They are metadata, not data. This means any downstream reference sheet you build will be incomplete unless you manually type those values in, which defeats the purpose of automation and introduces a maintenance burden every time someone adds a new choice option. The user is right to want a better approach.
What they need is a workaround that treats the choice column's schema as the data source. The practical solution involves using SharePoint's REST API or the OData feed to pull the field definition directly. In Power Query, you can construct a query to the SharePoint site's metadata endpoint, filter for the specific list and field, and extract the choices from the JSON or XML response. It takes a few steps, creating a blank query, entering the correct URL with the field's internal name, and parsing the results, but it produces a dynamic, always-current table of choice values. Once set up, that table becomes the single source of truth for any validation lists or dashboards you build.
This matters because the gap between what Power Query can do and what users assume it should do often stops people from exploring further. The user here did not give up. They asked the right question, identified the exact limitation, and looked for a smarter path. That is the mindset that unlocks cleaner, more reliable data systems. Our take is simple: treat choice columns as metadata worth extracting, invest the thirty minutes to build that schema query, and you will never have to manually update a reference sheet again. The alternative is brittle workflows that break the moment someone adds a new status. Do not settle for that.