Use the following formula
SQLColumns = (catalogName, schemaName, tableName, columnName, source) =>
let
OdbcSqlType.BIG_INT = -5,
OdbcSqlType.INTEGER = 4,
FixDataType = (dataType) =>
if dataType = OdbcSqlType.BIG_INT then
OdbcSqlType.INTEGER
else
dataType,
Transform = Table.TransformColumns(source, { { "DATA_TYPE", FixDataType } })
in
// the if statement conditions will force the values to evaluated/written to diagnostics
if (Diagnostics.LogValue("SQLColumns.TableName", tableName) <> "***" and Diagnostics.LogValue("SQLColumns.ColumnName", columnName) <> "***") then
let
// Outputting the entire table might be too large, and result in the value being truncated.
// We can output a row at a time instead with Table.TransformRows()
rows = Table.TransformRows(Transform, each Diagnostics.LogValue("SQLColumns", _)),
toTable = Table.FromRecords(rows)
in
Value.ReplaceType(toTable, Value.Type(Transform))
else
Transform,
and use the following SQL datatypes
SQL_TYPE =
[
// Base data types (sql.h)
UNKNOWN = 0,
NULL = 0,
CHAR = 1,
NUMERIC = 2,
DECIMAL = 3,
INTEGER = 4,
SMALLINT = 5,
FLOAT = 6,
REAL = 7,
DOUBLE = 8,
DATETIME = 9, // V3 Only
VARCHAR = 12,
// Unicode types (sqlucode.h)
WCHAR = -8,
WVARCHAR = -9,
WLONGVARCHAR = -10,
// Extended data types (sqlext.h)
INTERVAL = 10, // V3 Only
TIME = 10,
TIMESTAMP = 11,
LONGVARCHAR = -1,
BINARY = -2,
VARBINARY = -3,
LONGVARBINARY = -4,
BIGINT = -5,
TINYINT = -6,
BIT = -7,
GUID = -11, // V3 Only
// One-parameter shortcuts for date/time data types.
TYPE_DATE = 91,
TYPE_TIME = 92,
TYPE_TIMESTAMP = 93,
// SQL Server Types -150 to -159 (sqlncli.h)
SS_VARIANT = -150,
SS_UDT = -151,
SS_XML = -152,
SS_TABLE = -153,
SS_TIME2 = -154,
SS_TIMESTAMPOFFSET = -155
],
Want to learn more about Power Bi, Refer & learn Power bi
Also, check out our YouTube video to know the data visualization basics and best practices with Power BI
Learn Power BI Course from this video :
Intellipaat gives its learners an industrial training on Power BI Course. Enroll now to get a professional certificate!