Forum Discussion

cgeorgeot's avatar
cgeorgeot
Frequent Visitor
6 years ago
Solved

Column transformation

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":...
Need Help
  • cgeorgeot's avatar
    cgeorgeot
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

Hi, do you expect a table like this:

 

OwnerLocationenv
Cedricfranceprod

 

 

or do you want a list of strings like:

 

{"Column 1 : Owner | Value : Cedric",
"Column 2 : Location | Value : France",
"Column 3 : env| Value : prod"}

 

or other combination of the inputs?

 

cgeorgeot's avatar
cgeorgeot
Frequent Visitor
6 years ago

Hi thanks for your answer.

In order to be more precise: I have a column nammed Tags with, for each row/value,a string with a different length such as:

Column: Tags

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

Row 2 :"owner":"Chantal","location":"germany","env":"POC",.......

 

I want to get:

Column 1: owner

Row 1: cedric

Row 2: Chantal

 

Column 2: location

Row 1: france

Row 2: germany

....

 

Your first answer match my need !

Hope to be more clear, now...