Column transformation

[cgeorgeot]

Hello,

I need help to transform a column who contains a long string to (different length) into a multiple columns. (split the string)

The string located in the column nammed "Tags":

"owner":"cedric","location":"france","env":"prod",.......

I want to transform this string with this result:

Column 1 : Owner | Value : Cedric
Column 2 : Location | Value : France
Column 3 : env| Value : prod

Many thanks for your help

[Smauro]

Just replace nulls in [Tags] ...

Table.TransformColumns(usagedetails,{{"Tags", each if _ = null then "" else _, type text}})

[cgeorgeot · Accepted solution]

Hello

Thanks for your answer. I found a solution today:

let

    Source = AzureCostManagement.Tables("Enrollment Number", "xxxxx", 8, []),

    usagedetails = Source{[Key="usagedetails"]}[Data],

    #"Added Custom" = Table.AddColumn(usagedetails, "Tags JSON", each Text.Combine({"{ ", [Tags], " }"})),

    #"Parsed JSON" = Table.TransformColumns(#"Added Custom",{{"Tags JSON", Json.Document}}),

    #"Tags JSON développé" = Table.ExpandRecordColumn(#"Parsed JSON", "Tags JSON", {"CMDB", "Contact", "Environment"}, {"Tags JSON.CMDB", "Tags JSON.Contact", "Tags JSON.Environment"})

in

 #"Tags JSON développé"

[Anonymous]

I know (now) what is your request.

what I don't know is

which code, of the many received, did you use?
where does it give you error?
can you show the error screen?

hint:

try the code using a table with few rows, change the contents wich you can't expose and let we know what appens.