Struggling to transform list of properties into columns

[cfiessinger]

Trying to extract audit logs but running into the following error:

"Expression.Error: We cannot convert a value of type Record to type Text." 

I have a column called "ExtendedProperties" which contains 7 items separated by commas, but when I "Extract values" using comma separator, I get the convert error. 

Sample row I'm trying to convert into columns:

"ExtendedProperties":[{"Name":"PolicyId","Value":""},{"Name":"ItemId","Value":"01fb23729ef0dcf2b7d3e5101b783997e0f47149c51e5a7a22f0b755f25e5c30"},{"Name":"ItemSubject","Value":""},{"Name":"ItemAction","Value":"TeamsNotify"},{"Name":"ItemStatusAfterAction","Value":"TeamsNotify"},{"Name":"ItemClass","Value":"IPM.SkypeTeams.Message"},{"Name":"Sender","Value":"User1 <User1@tenant.onmicrosoft.com>"}]

Query in Advanced editor:

let
    Source = Csv.Document(File.Contents("C:\Users\chrisfie\Downloads\AuditLog.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CreationDate", type datetime}, {"UserIds", type text}, {"Operations", type text}, {"AuditData", type text}}),
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{{"AuditData", Json.Document}}),
    #"Expanded AuditData" = Table.ExpandRecordColumn(#"Parsed JSON", "AuditData", {"CreationTime", "Id", "CaseID", "CaseName", "ExtendedProperties"}, {"AuditData.CreationTime", "AuditData.Id", "AuditData.CaseID", "AuditData.CaseName", "AuditData.ExtendedProperties"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded AuditData", {"AuditData.ExtendedProperties", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

Ideally I want each of the 7 properties in ExtendedProperties to be new columns with values. 

Should I create a function to parse data? Any other solution please?

[Smauro]

Hi @cfiessinger 

You could try this:

let
    Source = Csv.Document(File.Contents("C:\Users\chrisfie\Downloads\AuditLog.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CreationDate", type datetime}, {"UserIds", type text}, {"Operations", type text}, {"AuditData", type text}}),
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{{"AuditData", Json.Document}}),
    #"Expanded AuditData" = Table.ExpandRecordColumn(#"Parsed JSON", "AuditData", {"CreationTime", "Id", "CaseID", "CaseName", "ExtendedProperties"}, {"AuditData.CreationTime", "AuditData.Id", "AuditData.CaseID", "AuditData.CaseName", "AuditData.ExtendedProperties"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded AuditData", {"AuditData.ExtendedProperties", each List.Accumulate(Json.Document(Text.Replace(_, """ExtendedProperties"":", "")) , [], (s,c) => Record.AddField(s, c[Name], c[Value]) )} ),
    #"Expanded Records" = Table.ExpandRecordColumn(#"Transformed ExtendedProperties", "AuditData.ExtendedProperties", {"PolicyId", "ItemId", "ItemSubject", "ItemAction", "ItemStatusAfterAction", "ItemClass", "Sender"})
in
    #"Expanded Records"

and if it gives you the same error, try this:

let
    Source = Csv.Document(File.Contents("C:\Users\chrisfie\Downloads\AuditLog.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CreationDate", type datetime}, {"UserIds", type text}, {"Operations", type text}, {"AuditData", type text}}),
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{{"AuditData", Json.Document}}),
    #"Expanded AuditData" = Table.ExpandRecordColumn(#"Parsed JSON", "AuditData", {"CreationTime", "Id", "CaseID", "CaseName", "ExtendedProperties"}, {"AuditData.CreationTime", "AuditData.Id", "AuditData.CaseID", "AuditData.CaseName", "AuditData.ExtendedProperties"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded AuditData", {"AuditData.ExtendedProperties", each List.Accumulate(_ , [], (s,c) => Record.AddField(s, c[Name], c[Value]) )} ),
    #"Expanded Records" = Table.ExpandRecordColumn(#"Transformed ExtendedProperties", "AuditData.ExtendedProperties", {"PolicyId", "ItemId", "ItemSubject", "ItemAction", "ItemStatusAfterAction", "ItemClass", "Sender"})
in
    #"Expanded Records"

[cfiessinger]

Thank you @Smauro for the quick response, tried both of your queries and getting the following error:

Expression.Error: The name 'Transformed ExtendedProperties' wasn't recognized. Make sure it's spelled correctly

[Smauro · Accepted solution]

@cfiessingersorry I just saw it.

Change

    #"Expanded Records" = Table.ExpandRecordColumn(#"Transformed ExtendedProperties", "AuditData.ExtendedProperties", {"PolicyId", "ItemId", "ItemSubject", "ItemAction", "ItemStatusAfterAction", "ItemClass", "Sender"})
in
    #"Expanded Records"

to

    #"Expanded Records" = Table.ExpandRecordColumn(#"Extracted Values", "AuditData.ExtendedProperties", {"PolicyId", "ItemId", "ItemSubject", "ItemAction", "ItemStatusAfterAction", "ItemClass", "Sender"})
in
    #"Expanded Records"

[cfiessinger]

You rock, THANK YOU so much and have a great day!

[Jimmy801]

Hello @cfiessinger 

you data shared is JSON, but it's missing the { and } at the beginning/end. You have to add this characters in a TransformColumns and then read with the function JSON and make some manipulation to expand. Here a practicable example for you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZHBSsQwEIZfRXJeSpJuiPXksnjoYZdC1UvTQ5pOJNo0SzIFi/ju7taL1D2It2H4vn8G/qYhijy8I4w99FUMJ4joICly13woctQezqMiVRicmctekY0iz3qYvteKfG5+YiWC/wVRZjueS16Apb2xvJN9DoJR1snbvCgkULuVbFsYwUBoqTm3tJNCWC5AmJxeO1JP3SsY/MM7O4MujCvwEbRPx4DOzlfTUeOUdhYh/kffDzqllVJWh6x+m0+wuNkBUtIvsLbrSw1xpT4liOxGTZTmZpnvz23pEbMwemdiSMFiZoJfiEtkS9r2Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ExtendedProperties = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ExtendedProperties", type text}}),
    AdaptJson = Table.TransformColumns(#"Changed Type",{{"ExtendedProperties",each "{"&_, type text}}),
    AdaptJson1 = Table.TransformColumns(AdaptJson,{{"ExtendedProperties", each _&"}", type text}}),
    #"Parsed JSON" = Table.TransformColumns(AdaptJson1,{},Json.Document),
    Expanded = Table.ExpandRecordColumn(#"Parsed JSON", "ExtendedProperties", {"ExtendedProperties"}, {"ExtendedProperties.1"}),
    TransformToTable = Table.TransformColumns
    (
        Expanded,
        {
            {
                "ExtendedProperties.1",
                each Table.PromoteHeaders(Table.Transpose(Table.FromRecords(_)))
            }
        }
    ),
    #"Expanded ExtendedProperties.1" = Table.ExpandTableColumn(TransformToTable, "ExtendedProperties.1", {"PolicyId", "ItemId", "ItemSubject", "ItemAction", "ItemStatusAfterAction", "ItemClass", "Sender"}, {"PolicyId", "ItemId", "ItemSubject", "ItemAction", "ItemStatusAfterAction", "ItemClass", "Sender"})
in
    #"Expanded ExtendedProperties.1"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[PhilipTreacy]

Hi @cfiessinger 

That data is being interpreted as a record as signified by the [] around it.  It's not text, hence the error you are getting.

If it was properly formatted text it would look like this

"[{""Name"":""PolicyId"",""Value"":""},{""Name"":""ItemId"",""Value"":""01fb23729ef0dcf2b7d3e5101b783997e0f47149c51e5a7a22f0b755f25e5c30""}"

Can you please share your source data.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[AlB]

Hi @cfiessinger 

Is this value:

"ExtendedProperties":[{"Name":"PolicyId","Value":""},{"Name":"ItemId","Value":"01fb23729ef0dcf2b7d3e5101b783997e0f47149c51e5a7a22f0b755f25e5c30"},{"Name":"ItemSubject","Value":""},{"Name":"ItemAction","Value":"TeamsNotify"},{"Name":"ItemStatusAfterAction","Value":"TeamsNotify"},{"Name":"ItemClass","Value":"IPM.SkypeTeams.Message"},{"Name":"Sender","Value":"User1 <User1@tenant.onmicrosoft.com>"}]

 the text you would have in a cell of the input table? Otherwise, can you share the file you are reading in your query,

AuditLog.csv

so that we can run some tests?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers