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": "tes...
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.

     

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.