Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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é"
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?
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])
@Anonymousnice 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
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
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
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
did you tried my solution?
@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
@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?
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"
@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"
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 @Anonymous
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.
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"
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"
Hello all, @Smauro @Mariusz @Anonymous
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.