Here is the code in full--comment at the very end explain the last step, a type cast, that caused the error, which went away once the type cast step was removed. Now: I am casting from text to logical using text values of 'False' and 'True'. Used to work in the past if I remember correctly.
let
source1 = Sql.Databases("DB_NAME"),
source2 = source1{ [Name="###"] }[Data],
dbo_vOLC = source2{ [Schema="dbo", Item="vOLC"] }[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_vOLC,{ {"ID_External", Int64.Type} }),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type", { {"FK_ID_###", "ID_###"} } ),
remove_cols = Table.StopFolding( //Table.Buffer( //
Table.RemoveColumns(
#"Renamed Columns",
{
"ID"
, "ID_OLC"
, "Longitude"
, "Latitude"
, "Is_remotely_managed"
, "Model"
, "FirmwareVersion"
, "HardwareAddress"
, "ControlSystem"
, "InstallationDate"
, "CommunicationStatus"
, "NumberOfMeteringChannels"
, "CLO_Enabled"
, "LastReportTime"
, "Is_metered"
, "UserSwitchType"
, "FactorySwitchType"
}
)
),
project_id = fnProjectID(),
getToken = () =>
let
URL = "https://api.###.com",
body = "app_key=###&app_secret=###&service=###&scope=###",
response = Web.Contents(
URL,
[
RelativePath = "oauth/accesstoken"
, Headers = [
#"Authorization"="Basic ###"
, #"Content-Type"="application/x-www-form-urlencoded"
]
, IsRetry = true
, Content = Text.ToBinary(body, BinaryEncoding.Base64)
]
),
jsonResponse = try Json.Document(response),
token = if jsonResponse[HasError] then
error jsonResponse[Error][Message]
else
jsonResponse[Value][token]
in
token,
getHandleID = (external_ID as number, token as text) =>
let
URL = "https://api.###.com",
relative_path = "###" & project_id & "###" & Number.ToText(external_ID) & "###",
headers = [
#"Authorization"="Bearer " & token
, #"Content-Type" = "application/json"
],
external_ID_txt = try Number.ToText(external_ID),
http_resp = if external_ID_txt[HasError] then
error "cannot convert external_ID from number to text"
else
try
Web.Contents(
URL
, [
RelativePath = relative_path
, Headers = headers
, IsRetry = true
]
),
response = if http_resp[HasError] then error http_resp[Error][Message]
else try Json.Document(http_resp[Value])
in
if response[HasError] then
response[Error][Message]
else
try response[Value]{0}[handleId] otherwise response[Value][Message],
token = getToken(),
add_handleID = Table.AddColumn(remove_cols, "handleID", each getHandleID([ID_External], token), type text),
type_change0 = Table.TransformColumnTypes( add_handleID, { {"handleID", type text} } ),
getRTData = (handle_ID as nullable text, token as text) as record =>
let
URL = "https://api.###.com",
relative_path = "###" & project_id & "###",
handleID_num = try Number.From(handle_ID),
rt_data = if handleID_num[HasError] then
[ERROR="Number.From(handle_ID) cannot convert; hanleID=" & handle_ID]
else
let
headers = [
#"Authorization" = "Bearer " & token
, #"Connection" = "keep-alive"
],
query = [handleId = handle_ID],
http_resp = try
Web.Contents(
URL
, [
RelativePath = relative_path
, Headers = headers
, IsRetry = true
, Query = query
]
)
,
data = if not http_resp[HasError] then try Json.Document(http_resp[Value])
else error http_resp[Error][Message],
result = if data[HasError] then [ERROR=data[Error][Message]]
else if Value.Is(data[Value], type list) and List.IsEmpty(data[Value]) then [ERROR="no data: wait for the next server refresh"]
// the API returns a list, ie [] JSON array, when data is available
else if Value.Is(data[Value], type list) and not List.IsEmpty(data[Value]) then data[Value]{0}
// the API returns a record, ie {} JSON object, only if there was an error
else if Value.Is(data[Value], type record) then [ERROR=data[Value][Message]] else null
in
result
in
// returns a record of real-time values or an error msg record
rt_data,
rt_table = Table.AddColumn(type_change0, "RT_DATA", each getRTData([handleID], token), type record),
expand_data = Table.ExpandRecordColumn(rt_table, "RT_DATA", {"status", "DateTime", "properties"}, {"status", "DateTime", "properties"}),
expand_prop_list = Table.ExpandListColumn(expand_data, "properties"),
#"Expanded properties" = Table.ExpandRecordColumn(expand_prop_list, "properties", {"Key", "Value", "Unit"}, {"Key", "Value", "Unit"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded properties",{"Unit"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Key] <> null)),
type_change1 = Table.TransformColumnTypes(#"Filtered Rows",{{"Key", type text}, {"Value", type text}}),
#"Pivoted Column" = Table.Pivot(type_change1, List.Distinct(type_change1[Key]), "Key", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","No","False",Replacer.ReplaceText,{"CLO Enabled"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Yes","True",Replacer.ReplaceText,{"CLO Enabled"})
// and here was the step to cast this [CLO Enabled] column to type logical, after the addition of which I got the OLE DB type mismatch error
in
#"Replaced Value1"
