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

Anonymousstrange, it seems to be working fine for me:

 



From the syntax page, it does accept 3 arguments.


cgeorgeot 

haha, no no, you should add the otherwise in the end, like so:

 

 

 

 

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

 

 

🙂

AnonymousYour solution is the record from list one, which is the one having the issues, because in case [Tags] is empty it errors calling an empty list's 1st and 2nd item

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Smauro  I use power query starting from excel. Do you use PBI, instead?

 

if so, do you know if it is possible and how to report the problem?