Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Set column types using json

Hi 🙂 

 

i have a custom power bi connector that loads data from various json sources.
i also have an endpoint that returns the data types for each column in the format

 

{
{
"columnName": "test1",
"columnType": "Number.Type"
},
{
"columnName": "test2",
"columnType": "Number.Type"
}, ...

}

 

I have tried this but always get an error:

ColumnTypeTable = Table.FromRecords(JsonColumnTypeData), //json data to table
ColumnTypeList = Table.ToList(Columns), //table to list
TestTable = Table.TransformColumnTypes(Source, ColumnTypeList )

 

How do i have to transform the columns type list so that i can pass them

 

Thank you so much!!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous, lbendlin,

    ColumnTypeTable = Table.FromRecords(JsonColumnTypeData), //json data to table
ColumntypeList = Table.TransformRows(dataTable, each {[columnName], MapColumnType([columnType])}),

     That's how it works for me. I have created a mapping method to map the text type from JSON to Power Query type.

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous, lbendlin,

    ColumnTypeTable = Table.FromRecords(JsonColumnTypeData), //json data to table
ColumntypeList = Table.TransformRows(dataTable, each {[columnName], MapColumnType([columnType])}),

     That's how it works for me. I have created a mapping method to map the text type from JSON to Power Query type.

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        yes of course 🙂 

         

        MapColumn = (columnType as text) as any => 
    let
        MappedType = 
            if (columnType = "Integer") then Int64.Type
            else
            if (columnType = "Text") then Text.Type
            else
            if (columnType = "Number") then Number.Type
            else
            if (columnType = "Date") then Date.Type
            else Text.Type
    in MappedType;

        I have changed the type of the service so that is more generic.

        Is the solution correct? 🙂

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    The type names in Power Query are different from what you get in the Json.  For example "Number.Type"  can be anything from "Int64.Type"  to "type number".  And the transform list expects just the column names and transforms, not the additional meta data from your Json.

     

    Table.TransformColumnTypes - PowerQuery M | Microsoft Docs

     

    Please provide sanitized sample data that fully covers your issue.
    Please show the expected outcome based on the sample data you provided.