Power Query is a self-service ETL tool from Microsoft which is able to extract data from a broad range of data sources, transform the data, and finally load the data to PowerPivot. However, I have recently found that Power Query could also be a very handy SharePoint Metadata viewer to inspect the schema of SharePoint sites, lists, content types… What we need is to make use of the Power Query OData connection to SharePoint Rest API and use the relation drill down feature to navigate through the SharePoint objects.

For example, we can connect to a SharePoint site (“https://linxiaodev.sharepoint.com/sites/POC/_api/web”) using the OData connection, and the Power Query will load all attributes of the site and also the links to drill down to the related Record or Table.

For example, we can drill down to the Lists table which load the metadata of all the lists in the site. One tip here is that you can select which columns of attributes to show instead of show a long list of all columns.

Then, you can further drill down to the Table or Record related to a list, e.g., the list of related Fields of the ‘Documents’ library list. In one of my previous blog post, I need to inspect the Hidden attribute of the LikedBy field and the tool I was using is SharePoint Manager, and now Power Query can be another option.

• Power BI/DAX/M