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

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi cgeorgeot 

 

Try this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyi/PSy2yilFKTk0pykyOUdKJUcrJT04syczPi1ECiqcVJeYlp4LFU/PKwEIFRfkpMUpKsToI3Xn5mDpLswnpQrITj6pciO05iSAIVlSSWlwCVaSgFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each
        let 
            splitByComma = Text.Split( [Tags], "," ), 
            transfotmToTable = Table.FromList( splitByComma, Splitter.SplitTextByDelimiter(":") ),
            transpose = Table.Transpose( transfotmToTable ),
            promoteHeaders = Table.PromoteHeaders( transpose, [PromoteAllScalars=true] )
        in 
            promoteHeaders

    ),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"owner", "location", "env", "lalala", " "}, {"owner", "location", "env", "lalala", " "}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Tags"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"owner", type text}, {"location", type text}, {"env", type text}, {"lalala", type text}, {" ", type any}})
in
    #"Changed Type"
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn