Forum Discussion

cgeorgeot's avatar
cgeorgeot
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":...
Need Help
  • cgeorgeot's avatar
    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é"

cgeorgeot's avatar
cgeorgeot
Frequent Visitor
6 years ago

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

Smauro's avatar
Smauro
Solution Sage
6 years ago

Just replace nulls in [Tags] ...

 

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

  • cgeorgeot's avatar
    cgeorgeot
    Frequent Visitor
    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é"