Using the Power Query Editor to format exported data

With the exported data, you may want to filter it using a tool such as the Power Query editor add-in for Microsoft Excel. Power Query is a data visualization product that's available for Microsoft Excel. It can be obtained from the Microsoft Download Center at https://www.microsoft.com/en-us/download/details.aspx?id=39379.

One of the features of Power Query is its ability to manipulate JSON data, which is important as the AuditData column contains a multi-property JSON object. Once you have split the object into multiple columns, you can use standard Excel filtering tools to locate your data. To use Power Query to split the AuditData values into separate columns, follow these steps:

  1. Ensure you have saved an audit log export.
  2. Launch Microsoft Excel, select the Data tab, and select Get Data | From File | From Text/CSV:

  1. Using the Import Data explorer window, navigate to the saved audit data CSV file, select it, and click Import.
  2. Ensure the delimiter setting is set to Comma and select Transform Data:

  1. Select the AuditData column, right-click it, and select Transform | JSON:

  1. By default, the column name, that is, AuditData, will be used as a prefix for all of the newly created column names (so you'll end up with column headers such as AuditData:Operation and AuditData:EventSource). If you want to change that behavior or deselect fields to be exported as columns, click the Expand icon at the top of the AuditData column. Click OK when you're finished:

  1. When you're done making changes, select Close & Load to load the transformed data into Excel.

For additional detailed step-by-step instructions, see Format the exported audit log using the Power Query Editor at https://docs.microsoft.com/en-us/microsoft-365/compliance/export-view-audit-log-records#step-2-format-the-exported-audit-log-using-the-power-query-editor under Export, Configure, and View Audit Log Records.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset