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é"

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