Column transformation

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"

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.

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.

I know (now) what is your request.

what I don't know is

which code, of the many received, did you use?
where does it give you error?
can you show the error screen?

hint:

try the code using a table with few rows, change the contents wich you can't expose and let we know what appens.

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"

BeforeAfter

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"

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

Just replace nulls in [Tags] ...

Table.TransformColumns(usagedetails,{{"Tags", each if _ = null then "" else _, type text}})