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

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

Can you show the code for MapColumnType ?

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