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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    try this:

     

     

     

        addPersCol = Table.AddColumn(youTab, "pers", each List.Accumulate(Text.Split(Text.Replace([Tags],"""",""),","),                                                                                                            [],(s,c)=>s&Record.FromList({Text.Split(c,":"){1}},{Text.Split(c,":"){0}}))),

in
    Table.FromRecords(addPersCol [pers])

     

     

    • Smauro's avatar
      Smauro
      Solution Sage
      6 years ago

      Anonymousnice record approach.

       

      However, in case there are different tags per row, we need to use a slightly better approach:

      1)After your addPersCol,

          RecFields = List.Union(Table.TransformColumns(Table.SelectColumns(addPersCol, {"pers"}), {{ "pers", Record.FieldNames, type list}})[pers]),

      To get all the possible tag names.
      2a) In case we want to keep the rest of the Table's columns:

          CorrectTags = Table.ExpandRecordColumn(addPersCol, "pers", RecFields),

      or 2b) in case we do not:

          CorrectTags = Table.FromRecords(addPersCol [pers], RecFields, MissingField.UseNull),

      3) get all new data types to text:

          #"Changed Type" = Table.TransformColumnTypes(CorrectTags,List.Transform(RecFields, each {_, type text}))
in
    #"Changed Type"




      ---
      And here's a dummy table to check the results:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyi/PSy2yilFKTk0pykyOUdKJUcrJT04syczPi1ECiqcVJeYlp4LFU/PKwEIFRfkpMUpKsToI3Xn5mDpLswnpQrITj6pciO05iSAIVlSSWlwCUhQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Tags", type text}}),
    addPersCol = Table.AddColumn(PreviousStep, "pers", each List.Accumulate(Text.Split(Text.Replace([Tags],"""",""),","), [],(s,c)=>s&Record.FromList({Text.Split(c,":"){1}},{Text.Split(c,":"){0}}))),
    RecFields = List.Union(Table.TransformColumns(Table.SelectColumns(addPersCol, {"pers"}), {{ "pers", Record.FieldNames, type list}})[pers]),
    CorrectTags = Table.ExpandRecordColumn(addPersCol, "pers", RecFields),
    //CorrectTags = Table.FromRecords(addPersCol [pers], RecFields, MissingField.UseNull),
    #"Changed Type" = Table.TransformColumnTypes(CorrectTags,List.Transform(RecFields, each {_, type text}))
in
    #"Changed Type"



       Cheers

      • Smauro's avatar
        Smauro
        Solution Sage
        6 years ago

        cgeorgeot,

         

        Seeing that your tags are almost in json format, you could also use this:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyi/PSy2yilFKTk0pykyOUdKJUcrJT04syczPi1ECiqcVJeYlp4LFU/PKwEIFRfkpMUpKsToI3Xn5mDpLswnpQrITj6pciO05iSAIVlSSWlwCUhQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Tags", type text}}),
    ToJson = Table.AddColumn(PreviousStep, "json", each Json.Document("{"&[Tags]&"}")),
    RecFields = List.Union(Table.TransformColumns(Table.SelectColumns(ToJson, {"json"}), {{ "json", Record.FieldNames, type list}})[json]),
    CorrectTags = Table.ExpandRecordColumn(ToJson, "json", RecFields),
    //CorrectTags = Table.FromRecords(ToJson[json], RecFields, MissingField.UseNull),
    #"Changed Type" = Table.TransformColumnTypes(CorrectTags,List.Transform(RecFields, each {_, type text}))
in
    #"Changed Type"

         

        Cheers