cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

1 ACCEPTED SOLUTION

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

View solution in original post

23 REPLIES 23
Super User II
Super User II

Hi, do you expect a table like this:

 

OwnerLocationenv
Cedricfranceprod

 

 

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?

 

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...

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])

 

 

@Rocco_sprmnt21nice record approach.

 

However, in case there are different tags per row, we need to use a slightly better approach:

1)After your addPersCol,

    RecFields = List.Union(Table.TransformColumns(Table.SelectColumns(addPersCol, {"pers"}), {{ "pers", Record.FieldNames, type list}})[pers]),

To get all the possible tag names.
2a) In case we want to keep the rest of the Table's columns:

    CorrectTags = Table.ExpandRecordColumn(addPersCol, "pers", RecFields),

or 2b) in case we do not:

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

3) get all new data types to text:

    #"Changed Type" = Table.TransformColumnTypes(CorrectTags,List.Transform(RecFields, each {_, type text}))
in
    #"Changed Type"




---
And here's a dummy table to check the results:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyi/PSy2yilFKTk0pykyOUdKJUcrJT04syczPi1ECiqcVJeYlp4LFU/PKwEIFRfkpMUpKsToI3Xn5mDpLswnpQrITj6pciO05iSAIVlSSWlwCUhQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Tags", type text}}),
    addPersCol = Table.AddColumn(PreviousStep, "pers", each List.Accumulate(Text.Split(Text.Replace([Tags],"""",""),","), [],(s,c)=>s&Record.FromList({Text.Split(c,":"){1}},{Text.Split(c,":"){0}}))),
    RecFields = List.Union(Table.TransformColumns(Table.SelectColumns(addPersCol, {"pers"}), {{ "pers", Record.FieldNames, type list}})[pers]),
    CorrectTags = Table.ExpandRecordColumn(addPersCol, "pers", RecFields),
    //CorrectTags = Table.FromRecords(addPersCol [pers], RecFields, MissingField.UseNull),
    #"Changed Type" = Table.TransformColumnTypes(CorrectTags,List.Transform(RecFields, each {_, type text}))
in
    #"Changed Type"



 Cheers




Feel free to connect with me:
LinkedIn

@cgeorgeot,

 

Seeing that your tags are almost in json format, you could also use this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyi/PSy2yilFKTk0pykyOUdKJUcrJT04syczPi1ECiqcVJeYlp4LFU/PKwEIFRfkpMUpKsToI3Xn5mDpLswnpQrITj6pciO05iSAIVlSSWlwCUhQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"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"

 

Cheers




Feel free to connect with me:
LinkedIn

Hello,

First, thanks for your answer. Unfortunately, it does'nt works... PowerBi said that there was not enough elements to finish operation... (Details: List)

 

let
Source = AzureCostManagement.Tables("Enrollment Number", "xxxxx", 1, []),
usagedetailsamortized = Source{[Key="usagedetailsamortized"]}[Data],
PreviousStep = Table.TransformColumnTypes(usagedetailsamortized ,{{"Tags", type text}}),
addPersCol = Table.AddColumn(PreviousStep, "pers", each List.Accumulate(Text.Split(Text.Replace([Tags],"""",""),","), [],(s,c)=>s&Record.FromList({Text.Split(c,":"){1}},{Text.Split(c,":"){0}}))),
RecFields = List.Union(Table.TransformColumns(Table.SelectColumns(addPersCol, {"pers"}), {{ "pers", Record.FieldNames, type list}})[pers]),
CorrectTags = Table.ExpandRecordColumn(addPersCol, "pers", RecFields),
//CorrectTags = Table.FromRecords(addPersCol [pers], RecFields, MissingField.UseNull),
#"Changed Type" = Table.TransformColumnTypes(CorrectTags,List.Transform(RecFields, each {_, type text}))
in
#"Changed Type"

 

Any idea ?

Hi @cgeorgeot 

 

You propably got some empty Tags.

Seems like you're using the first approach, try enclosing the column step with try / otherwise []:

Add.Column ..... each try ..Previous Code.. otherwise [])

 

You should also probably use the json one, since it is probably more optimised and woundn't need this modification.

Cheers




Feel free to connect with me:
LinkedIn

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

@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?

 

 

@Rocco_sprmnt21strange, it seems to be working fine for me:

 

image.png

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 []),

 

 

🙂

@Rocco_sprmnt21Your 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




Feel free to connect with me:
LinkedIn

@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?

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

 

 

 

 

@Rocco_sprmnt21yes 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"

image.pngimage.png

 

 

 

 

 

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

 




Feel free to connect with me:
LinkedIn

Hi,

 

For each query, it takes a longgggg time, more than 1 hour ! I didn't see results yet...

 

Any idea why it takes a long time ? To many records ?

@Smauro @Rocco_sprmnt21 

Hi guys !

I come back to you, PowerBI said that he can't convert value type LIST into Table.

Thanks

this means that pbi receives a list while waiting for a table.
but where and why, who knows?

 

you should provide more details, otherwise how do you do an analysis?

Sorry for the few informations I gave. Below a link to a picture.

Link to the image 

My problem is to split TAGS 

- example: "stage":"all","Scope":"All",....

But the order is randomized.

 

I would like to split TAGS values into columns.

 

Already discuss here but without good result...

https://community.powerbi.com/t5/Desktop/Azure-Cost-Management-Splitting-Out-Tags/td-p/948823

 

I hope I give you right information now.

Thanks again for your help.

 

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


 

Hi @cgeorgeot,


I knew you'd have different tags, that's why I've been mentioning the extra step of getting the columns from all the record fields. This is what's probably taking a lot of time. If you know the full list of tags and they're not that many then you could hardcode them and win a lot of computing time by just removing completely empty columns in the end.


The code I've provided should work for you, if not, please show us your code and where this list error appears, because in my environment it runs as it should:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBBDoIwEEWvMpm1J3DjVk/ggrIYYZSGdoaUQuPthUIUQ6Jp0sXP++83LQrUJByOBiuug60MHgw6rShaFYNTfg8kFeecZcxRF7Q2iOXh0xbdN4f2X2uz+YPyy7qj+WQoch9XCPJtkLrO2c14TZFuk7ztV9DgxUNqKPLIASwkkrjIwjAPwN7hVFu4Nhx44smDaHrL8uMydaaR4akD9MwCsbE9qPBpUX59SJaQgPX04NlUvgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Tags", type text}}),


    //THIS IS THE CODE HERE
    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),
    //Choose the one down if you'd like the results in a new table.
    //CorrectTags = Table.FromRecords(ToJson[json], RecFields, MissingField.UseNull),
    #"Changed Type" = Table.TransformColumnTypes(CorrectTags,List.Transform(RecFields, each {_, type text}))
in
    #"Changed Type"


BeforeBeforeAfterAfter




Feel free to connect with me:
LinkedIn

Hello all, @Smauro @Mariusz @Rocco_sprmnt21 

 

I have this message:

{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"We cannot convert the value null to type Record."}}],"exceptionCulprit":1}}} Table: Usage details.

 

The code:

 

let
Source = AzureCostManagement.Tables("Enrollment Number", "xxxxxx", 4, []),
usagedetails = Source{[Key="usagedetails"]}[Data],

PreviousStep = Table.TransformColumnTypes(usagedetails,{{"Tags", type text}}),

//THIS IS THE CODE HERE
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),
//Choose the one down if you'd like the results in a new table.
//CorrectTags = Table.FromRecords(ToJson[json], RecFields, MissingField.UseNull),
#"Changed Type" = Table.TransformColumnTypes(CorrectTags,List.Transform(RecFields, each {_, type text}))
in
#"Changed Type"

 

Thanks... 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors