Forum Discussion

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":"cedric","location":"france","env":"prod",.......

I want to transform this string with this result:

Column 1 : Owner | Value : Cedric
Column 2 : Location | Value : France
Column 3 : env| Value : prod

Many thanks for your help

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

23 Replies

Anonymous
Not applicable
6 years ago
Hi, do you expect a table like this:

Owner Location env
Cedric france prod

or do you want a list of strings like:

{"Column 1 : Owner | Value : Cedric",
"Column 2 : Location | Value : France",
"Column 3 : env| Value : prod"}

or other combination of the inputs?
- cgeorgeot
  Frequent Visitor
  6 years ago
  Hi thanks for your answer.
  In order to be more precise: I have a column nammed Tags with, for each row/value,a string with a different length such as:
  Column: Tags
  Row 1: "owner":"cedric","location":"france","env":"prod",.......
  Row 2 :"owner":"Chantal","location":"germany","env":"POC",.......
  
  I want to get:
  Column 1: owner
  Row 1: cedric
  Row 2: Chantal
  
  Column 2: location
  Row 1: france
  Row 2: germany
  ....
  
  Your first answer match my need !
  Hope to be more clear, now...
  - 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])