Column transformation

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