m/PowerQuery: How can I convert a table column into a readable text column?

Question

I'm dealing with a PowerShell audit log, which I have imported from an XML dump.

Each row is a PowerShell event, including various fields such as:

Attribute:Cmdlet (the name of the command that was executed)
Attribute:Caller (the username of the administrator who executed the command)
CmdletParameters.Parameter (input parameters that were provided for the command)

As you can see from the screenshot, CmdletParameters.Parameter is a table column.
This is the column I am trying to expand.

Each PowerShell event can have an unlimited number of parameters.
(The maximum among this dataset is 17 parameters for a single event.)

For example, here's a command with two parameters:
Set-Mailbox -Identity Username1 -AccountDisabled FALSE

In this example, within the CmdletParameters.Parameter table column, there would be two rows:

I would like to create two custom columns called ParameterNames and ParameterValues
In this example, these custom columns would contain the text:

EDIT:
A commenter requested an example of the originating XML data.
I've included two event rows, with private information redacted.

<?xml version="1.0" encoding="utf-16"?>
<SearchResults>
  <Event Caller="AdminUser1" Cmdlet="New-AdminAuditLogSearch" RunDate="2019-04-04T21:49:52+00:00" Succeeded="true" ObjectModified="ObjectUUID1" ExternalAccess="false" OriginatingServer="ServerName1 (IPAddress1)" ClientIP="[IPAddress2]:Port2">
    <CmdletParameters>
      <Parameter Name="StartDate" Value="4/3/2019 12:00:00 AM" />
      <Parameter Name="EndDate" Value="4/4/2019 12:00:00 AM" />
      <Parameter Name="ExternalAccess" Value="True" />
      <Parameter Name="StatusMailRecipients" Value="AdminUser1" />
      <Parameter Name="Name" Value="External admin audit log" />
    </CmdletParameters>
  </Event>
  <Event Caller="AdminUser2" Cmdlet="New-AdminAuditLogSearch" RunDate="2019-04-04T21:33:08+00:00" Succeeded="true" ObjectModified="ObjectUUID2" ExternalAccess="false" OriginatingServer="ServerName2 (IPAddress3)" SessionId="ObjectUUID3" ClientIP="[IPAddress4]:Port4">
    <CmdletParameters>
      <Parameter Name="StartDate" Value="4/3/2019 12:00:00 AM" />
      <Parameter Name="EndDate" Value="4/4/2019 12:00:00 AM" />
      <Parameter Name="StatusMailRecipients" Value="AdminUser2" />
    </CmdletParameters>
  </Event>
</SearchResults>

EXTRA BONUS:

As I mentioned above, there are a maximum of 17 parameters per row in this dataset.
If I were to also create 17 columns for ParameterName1..ParameterName17, how could I populate these columns?
Is it easier to convert the table column to a single text column first, and then parse that text into separate columns?

Can you give a couple of representative examples of these tables in XML form? — Alexis Olson
– Alexis Olson, Commented Apr 9, 2019 at 15:02
@AlexisOlson I added two event rows from one of the XML dumps, so you can see what the schema looks like. — Giffyguy
– Giffyguy, Commented Apr 9, 2019 at 15:24
Great! You probably want to close your SearchResults tag though. — Alexis Olson
– Alexis Olson, Commented Apr 9, 2019 at 15:25
@AlexisOlson Well of course. XD The XML file is ~8MB, I wasn't going to post the whole thing. lol I'll add the closing tag, to help avoid triggering readers' OCD — Giffyguy
– Giffyguy, Commented Apr 9, 2019 at 15:28

Alexis Olson · Accepted Answer · 2019-04-09 15:37:30Z

1

I think the best option here would be to expand those parameter tables to new rows and then pivot on the Attribute:Name column.

You should be able to do this by just clicking on buttons in the GUI, but here's what the M code looks like:

let
    XMLTable = <XML Table Here>,
    #"Expanded CmdletParameters" = Table.ExpandTableColumn(XMLTable, "CmdletParameters", {"Parameter"}, {"Parameter"}),
    #"Expanded Parameter" = Table.ExpandTableColumn(#"Expanded CmdletParameters", "Parameter", {"Attribute:Name", "Attribute:Value"}, {"Attribute:Name", "Attribute:Value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Parameter", List.Distinct(#"Expanded Parameter"[#"Attribute:Name"]), "Attribute:Name", "Attribute:Value")
in
    #"Pivoted Column"

Note that when pivoting on Attribute:Name, I choose Attribute:Value for the Values Column and Don't Aggregate under Advanced options.

The end result should look like this (except that I removed some of the other Attribute: columns for visibility.

answered Apr 9, 2019 at 15:37

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Giffyguy Over a year ago

I'm not sure if this pivot is working as you intended. You used List.Distinct but the only (possibly) unique column is Attribute:RunDate. I'm getting the error Expression.Error: The index is outside the bounds of the record and I think that error is related to the fact that the pivoted column(s) are not unique? Or am I reading this incorrectly?

Giffyguy Over a year ago

Based on the row numbers referenced in the error message, it looks like it fails whenever it sees the same parameter name+value pair, repeated in the dataset: i.sstatic.net/FGIU7.png In this example screenshot, the error message said it failed at row 10.

Alexis Olson Over a year ago

Try adding an index column (Add Column tab > Index Column) before pivoting to get unique rows.

Gangula · Accepted Answer · 2019-07-10 15:29:21Z

If your 3rd image is the output that you want, you can achieve that by using Text.Combine in a custom column.

Below is the Query step that you need to add for 2 columns

Add_Parameter_Names= Table.AddColumn(#"Previous Step", "ParameterNames", each Text.Combine([CmdletParameters.Parameter][Attribute:Name], ", "))
Add_Parameter_Values = Table.AddColumn(#"Add_Parameter_Names", "ParameterValues", each Text.Combine([CmdletParameters.Parameter][Attribute:Value], ", "))

Note: If you have null values in Attribute:Name/Attribute:Value columns, I would recommend adding a conditional column.

And as for your bonus question, I think you can decide that for yourself once you check this output. Please do comment if you need anything.

Collectives™ on Stack Overflow

m/PowerQuery: How can I convert a table column into a readable text column?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related