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.

     

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

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