Method identify quickly excel contains Pivot table added to data model or excel contains measure

[gazmoz17]

Hi,

Is there a quick way (ideally in fle explorer) which would show me this excel has a pivot table (added to data model) or this excel contains Pivot table with Measures? Or tbh would be handy if was a quick identifier to show just this excel contains a pivot table. The same identifier would be handy if shown these files are all linked to power query... I digress.

If not from file type property in explorer (which would be ideal so dont have to open multiple saved excels)....is there a way to export all measures from pivot table into a work sheet?

Many Thanks
Gareth

 

[aaewalsh]

In File Explorer, there is no built-in way to quickly identify whether an Excel file contains a pivot table or a pivot table with measures. However, you can check the file's properties to see if it is an Excel file and then open it in Excel to check if it contains a pivot table.​

As for exporting measures from a pivot table, you can use the "Field List" pane in Excel. To do this:

1. Open the Excel file that contains the pivot table.
2. Click on the pivot table to select it.
3. Go to "Analyze" tab in the ribbon menu then click on "Fields, Items & Sets".
4. From the "Fields, Items & Sets" menu, select "Measure Group" and then "Export"
5. It will open a new worksheet which will show all the measures in the pivot table.

Alternatively you can use the VBA to list down all the measures in pivot table in active sheet of workbook using the following code.

Sub PivotMeasures()
    Dim pt As PivotTable, pf As PivotField
    Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each pt In ActiveSheet.PivotTables
        For Each pf In pt.DataFields
            ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = pf.Name
        Next pf
    Next pt
End Sub

Please note that this will list all the measures in the active sheet of the workbook and if multiple pivot table exist in the workbook then it will list all the measures in those pivot tables as well.

 

[gazmoz17]

Superb mate thanks very much for taking the time there and the additional vba option . I need to organise my files better I thought it was asking too much there just being a property without opening the file.

 

[aaewalsh]

No issue at all. If you can just click solved if your happy so we can close the post. Thanks again. Adam