Approval Status odata_ModerationStatus is only Approved

Hi Rosie 

When connecting to a SharePoint library in Power BI, the odata_ModerationStatus field should indeed show all the moderation statuses, including "Pending," "Approved," "Rejected," etc. However, if you're seeing only "Approved" statuses, it could be due to a few potential issues with either the data loading or the permissions/access setup.

Here are some steps and possible solutions you can try:

1. Check Permissions:

Make sure that the account used to connect Power BI to the SharePoint library has sufficient permissions to see the document approval statuses. Sometimes, limited permissions may restrict visibility to only "Approved" documents.

2. Use the SharePoint API:

You may not be seeing all the relevant metadata fields because Power BI's default connection might not expose them correctly. You can directly query SharePoint using the SharePoint API to retrieve more comprehensive data, including approval statuses. Here’s how you can do it:

• Use the following SharePoint API query in Power BI (via the OData feed):

  plaintext

  Copy code

  https://[Your_SharePoint_Site]/_vti_bin/ListData.svc/[Your_Library_Name]

• In this case, check if Approval Status (or other relevant fields) are properly populated and exposed by the API. You might find additional metadata fields not exposed by Power BI’s default connector.

  3. Check for Alternate Columns:

  SharePoint sometimes has multiple columns that can indicate approval status. You might want to explore the following columns in your SharePoint list:

  • Status: Sometimes SharePoint libraries have a field called Status or a variation that reflects the approval process.
  • Approval Comments or Approval Status: Depending on your SharePoint setup, there could be other custom fields tracking approval steps.
  • Versioning: Ensure that versioning is not impacting the visibility of the approval status in Power BI.

    4. Try Refreshing the Data Model:

    If you have recently updated documents in SharePoint or changed their statuses, and Power BI is still showing outdated statuses, try refreshing your data model. Sometimes Power BI's cached data might not reflect the latest changes in SharePoint.

    5. Check for Custom Views or Filters:

    Ensure that your SharePoint library view doesn’t have filters applied that could restrict the documents you're seeing in Power BI to only "Approved" statuses. Ensure you're pulling data from a view that shows all documents, regardless of status.

    6. Custom Field Mappings:

    If you are using custom workflows or SharePoint Designer workflows, it is possible that the approval status is stored in a custom field that is not automatically exposed in Power BI. You may need to identify that custom field and make sure it is selected when importing the data.

    Example of Dynamic Query to Extract Status:

    Here’s how you can use Power Query to dynamically extract and analyze the Approval Status:

     

    M

    Copy code

    let Source = OData.Feed("https://[Your_SharePoint_Site]/_vti_bin/ListData.svc/[Your_Library_Name]"), FilteredRows = Table.SelectRows(Source, each [ModerationStatus] <> null) in FilteredRows

     

    This query dynamically extracts rows where the ModerationStatus is available and can help you identify all possible approval statuses.

    If none of these solutions help, I would also consult the SharePoint forum:

    https://techcommunity.microsoft.com/t5/sharepoint/bd-p/SharePoint_General

    If my answer was helpful please give me a Kudos and accept as a Solution.