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

cgeorgeot's avatar
cgeorgeot
Frequent Visitor
6 years ago

Hi,

 

I add TRY but when I add otherwise, indicated that is not correct : "EOF excepted"...

 

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

 

When I just let try, this is very long.... I don't have the result yet...

 

Thanks again

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Smauro  very good job!

 

I also tried for the more general case this:

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

 

but seems the function expect only 1 or 2 parameters, then you can't give the missingfield.usenull

 

cgeorgeot 

did you tried my solution?