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

cgeorgeot 

try this, but in PBI not in excel 😁

 

 

 

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


    #"tab da records" = Table.FromRecords(#"crea record"[pers],null,MissingField.UseNull)

in
    #"tab da records"

 

 

 

 

Smauro's avatar
Smauro
Solution Sage
6 years ago

Anonymousyes I'm using PBI.

 

Your last solution is similar to the first one I gave, but more prone to errors: when using Table.FromRecords with null in ColumnNames PowerBI tends to be lazy and gets the FieldNames of the first record.

 

Try Table.FromRecords({[a= 1, b=2], [a=1, c=3], [d=4, a=3]}, null, MissingField.UseNull) to understand what I mean. That's why I used a list of FieldNames.

More, I'll say it again, this solution will return error if [Tags] is empty.

 

See:

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]),
    Foglio2_Sheet = Table.TransformColumnTypes(Source,{{"Tags", type text}}),
  #"crea record" = Table.AddColumn(Foglio2_Sheet, "pers", each List.Accumulate(Text.Split(Text.Replace([Tags],"""",""),","),
                                        [],(s,c)=>s&Record.FromList({Text.Split(c,":"){1}},{Text.Split(c,":"){0}}))),


    #"tab da records" = Table.FromRecords(#"crea record"[pers],null,MissingField.UseNull)

in
    #"tab da records"

 

 

 

 

 

cgeorgeotto avoid all try/otherwise just use the last solution I gave:

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