none
Convert EventData with tag names to name value data RRS feed

  • Question

  • Hi I have the following data;

    Index, Name, Value
    41, EventData, <Permissions granted>Read<\Permissions granted><Shared by>name@company.com.au<\Shared by><Invited account>name@external.com.au<\Invited account>

    which I want to transform into;

    Index, Name1, Value1
    41,"Permission Granted","Read"
    41, "Shared by","name@company.com.au"
    41, "Invited account","name@external.com"

    The data is actually a subset of data extracted from Office365 Unified Audit Log. Various transformations have been performed to get it to this point however the above is raw eventdata contained within the audit data which we need to breakdown for further analysis.

    If anyone has a suggestions or a working example, it would be much appreciated.

    Thanks.

    Thursday, January 12, 2017 5:26 AM

Answers

  • This would be one way to go:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Name", type text}, {"Value", type text}}),
        SplitAndSpread = Table.AddColumn(#"Changed Type", "SplitAndSpread", each Text.Split([Value], "><")),
        #"Expanded SplitAndSpread" = Table.ExpandListColumn(SplitAndSpread, "SplitAndSpread"),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Expanded SplitAndSpread","SplitAndSpread",Splitter.SplitTextByEachDelimiter({"<\"}, QuoteStyle.Csv, true),{"SplitAndSpread.1"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Delimiter1","SplitAndSpread.1",Splitter.SplitTextByDelimiter(">", QuoteStyle.Csv),{"Name1", "Name2"}),
        Cleanup = Table.ReplaceValue(#"Split Column by Delimiter","<","",Replacer.ReplaceText,{"Name1"})
    in
        Cleanup


    Imke Feldmann TheBIccountant.com

    • Marked as answer by naleo96 Monday, January 16, 2017 12:25 AM
    Thursday, January 12, 2017 7:25 AM
    Moderator

All replies

  • This would be one way to go:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Name", type text}, {"Value", type text}}),
        SplitAndSpread = Table.AddColumn(#"Changed Type", "SplitAndSpread", each Text.Split([Value], "><")),
        #"Expanded SplitAndSpread" = Table.ExpandListColumn(SplitAndSpread, "SplitAndSpread"),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Expanded SplitAndSpread","SplitAndSpread",Splitter.SplitTextByEachDelimiter({"<\"}, QuoteStyle.Csv, true),{"SplitAndSpread.1"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Delimiter1","SplitAndSpread.1",Splitter.SplitTextByDelimiter(">", QuoteStyle.Csv),{"Name1", "Name2"}),
        Cleanup = Table.ReplaceValue(#"Split Column by Delimiter","<","",Replacer.ReplaceText,{"Name1"})
    in
        Cleanup


    Imke Feldmann TheBIccountant.com

    • Marked as answer by naleo96 Monday, January 16, 2017 12:25 AM
    Thursday, January 12, 2017 7:25 AM
    Moderator
  • Thanks for the solution.

    The bit I was stuck on was resolved by the 3rd step ... Table.AddColumn etc.

    As always, obvious once you see it set out for you.

    Thanks again.

    Monday, January 16, 2017 12:27 AM