Set column types using json

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[lbendlin]

Can you show the code for MapColumnType ?

[Anonymous]

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]

if it works for your scenario then that's all that matters. Setting the alternate to Text makes sense.

[Anonymous]

Hi @Anonymous ,

Please refer to the following links.

Need help expanding JSON list in column... 

Multi-Dimensional Json file to Table 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

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.