m/PowerQuery: как преобразовать столбец таблицы в читаемый текстовый столбец?

Question

Я имею дело с журналом аудита PowerShell, который я импортировал из дампа XML.

Каждая строка представляет собой событие PowerShell, включая различные поля, такие как:

• Attribute:Cmdlet (название команды, которая была выполнена)
• Attribute:Caller (имя пользователя администратора, который выполнил команду)
• CmdletParameters.Parameter (входные параметры, которые были предоставлены для команды)

Как вы можете видеть на скриншоте, CmdletParameters.Parameter столбец таблицы
Это колонка, которую я пытаюсь расширить.

Каждое событие PowerShell может иметь неограниченное количество параметров.
(Максимум среди этого набора данных - 17 параметров для одного события.)

Например, вот команда с двумя параметрами:
Set-Mailbox -Identity Username1 -AccountDisabled FALSE

В этом примере в пределах CmdletParameters.Parameter столбец таблицы, будет две строки:

Я хотел бы создать два пользовательских столбца под названием ParameterNames а также ParameterValues
В этом примере эти пользовательские столбцы будут содержать текст:

РЕДАКТИРОВАТЬ:
Комментатор запросил пример исходных данных XML.
Я включил две строки событий с закрытой информацией.

<?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>

ДОПОЛНИТЕЛЬНЫЙ БОНУС:

Как я упоминал выше, в этом наборе данных может быть не более 17 параметров в строке.
Если бы я также создал 17 столбцов для ParameterName1..ParameterName17Как я мог заполнить эти столбцы?
Легче ли сначала преобразовать столбец таблицы в один текстовый столбец, а затем проанализировать этот текст в отдельные столбцы?

Answer 1

Я думаю, что лучшим вариантом здесь будет расширить эти таблицы параметров до новых строк, а затем повернуть на Attribute:Name колонка.

Вы должны быть в состоянии сделать это, просто нажав на кнопки в графическом интерфейсе, но вот как выглядит код M:

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"

Обратите внимание, что при повороте на Attribute:Name, Я выбираю Attribute:Value для столбца значений и не агрегировать в дополнительных параметрах.

Конечный результат должен выглядеть следующим образом (за исключением того, что я удалил некоторые другие Attribute: столбцы для наглядности.

Answer 2

Если ваше третье изображение - это результат, который вы хотите, вы можете добиться этого, используя Text.Combine в настраиваемом столбце.

Ниже приведен шаг запроса, который вам нужно добавить для 2 столбцов.

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], ", "))

Примечание: если у вас естьnull значения в столбцах Attribute:Name/Attribute:Value, я бы рекомендовал добавить условный столбец.

Что касается вашего вопроса о бонусе, я думаю, вы можете решить это сами, проверив этот вывод. Пожалуйста, прокомментируйте, если вам что-то нужно.