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

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

    • cgeorgeot's avatar
      cgeorgeot
      Frequent Visitor
      6 years ago

      Hello,

      First, thanks for your answer. Unfortunately, it does'nt works... PowerBi said that there was not enough elements to finish operation... (Details: List)

       

      let
      Source = AzureCostManagement.Tables("Enrollment Number", "xxxxx", 1, []),
      usagedetailsamortized = Source{[Key="usagedetailsamortized"]}[Data],
      PreviousStep = Table.TransformColumnTypes(usagedetailsamortized ,{{"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"

       

      Any idea ?

      • Smauro's avatar
        Smauro
        Solution Sage
        6 years ago

        Hi cgeorgeot 

         

        You propably got some empty Tags.

        Seems like you're using the first approach, try enclosing the column step with try / otherwise []:

        Add.Column ..... each try ..Previous Code.. otherwise [])

         

        You should also probably use the json one, since it is probably more optimised and woundn't need this modification.

        Cheers