How to use Dynamic Transformations in a custom connector

[Wanted]

Hi everyone !

I'm not a very good developer and I'm using Microsoft's trippin example to create my custom connector.

I am using an api to get data from web content, my is that the api's response is not always the same and so I am getting this error

Expression.Error: The field 'communication_language__formated' of the record wasn't found.

It is true that in the result of the query, some rows have a value and others do not.
I want to find some help to embed a "null" value before the row is in error. Or find a solution that allows me to just override the fact that a value is missing but does not inform me of the whole line in error.

When I'm not using a custom connector, I don't get this error.

It is my custom connector which is based on the trip pin connector and which creates this error.

Spoiler

section TripPin;

BaseUrl = "https://company.com/api/";

[DataSource.Kind="TripPin", Publish="TripPin.Publish"]
shared TripPin.Contents = () => TripPinNavTable(BaseUrl) as table;

DefaultRequestHeaders = [
#"Accept" = "application/json;odata.metadata=minimal", // column name and values only
#"OData-MaxVersion" = "4.0", // we only support v4
#"Authorization" = "Bearer token"
];

TripPinImpl = (url as text) =>
let
json = Value.WaitFor(

(iteration) =>

let

content = Web.Contents(url, [ Headers = DefaultRequestHeaders ], [ManualStatusHandling = {429}]),

buffered = Binary.Buffer(content),

status = Value.Metadata(content)[Response.Status],

actualResult = if status = 429 then null

else Json.Document(Text.FromBinary(buffered))

in

actualResult,

(iteration) => #duration(0, 0, 0, 30),

10

)

in

json;

TripPin.Feed = (url as text) as table => GetAllPagesByNextLink(url);

// Data Source Kind description
TripPin = [
Authentication = [
Anonymous = []
],
Label = "TripPin Part 1 - OData"
];

// Data Source UI publishing description
TripPin.Publish = [
Beta = true,
Category = "Other",
ButtonText = { "TripPin OData", "TripPin OData" }
];

TripPinNavTable = (url as text) as table =>
let
entitiesAsTable = Table.FromList(RootEntities, Splitter.SplitByNothing()),
rename = Table.RenameColumns(entitiesAsTable, {{"Column1", "Name"}}),
// Add Data as a calculated column
withData = Table.AddColumn(rename, "Data", each TripPin.Feed(Uri.Combine(url, [Name])), Uri.Type),
// Add ItemKind and ItemName as fixed text values
withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
// Indicate that the node should not be expandable
withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical),
// Generate the nav table
navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
navTable;


Table.ToNavigationTable = (
table as table,
keyColumns as list,
nameColumn as text,
dataColumn as text,
itemKindColumn as text,
itemNameColumn as text,
isLeafColumn as text
) as table =>
let
tableType = Value.Type(table),
newTableType = Type.AddTableKey(tableType, keyColumns, true) meta 
[
NavigationTable.NameColumn = nameColumn, 
NavigationTable.DataColumn = dataColumn,
NavigationTable.ItemKindColumn = itemKindColumn, 
Preview.DelayColumn = itemNameColumn, 
NavigationTable.IsLeafColumn = isLeafColumn
],
navigationTable = Value.ReplaceType(table, newTableType)
in
navigationTable;
RootEntities = {
"jobs/&limit=200",
"candidates/&limit=200",
"candidates/evaluations/&limit=200",
"employees/"
};

// The getNextPage function takes a single argument and is expected to return a nullable table
Table.GenerateByPage = (getNextPage as function) as table =>
let 
listOfPages = List.Generate(
() => getNextPage(null), // get the first page of data
(lastPage) => lastPage <> null, // stop when the function returns null
(lastPage) => getNextPage(lastPage) // pass the previous page to the next function call
),
// concatenate the pages together
tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
firstRow = tableOfPages{0}?
in
// if we didn't get back any pages of data, return an empty table
// otherwise set the table type based on the columns of the first page
if (firstRow = null) then
Table.FromRows({})
else 
Value.ReplaceType(
Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
Value.Type(firstRow[Column1])
);

// Read all pages of data.
// After every page, we check the "NextLink" record on the metadata of the previous request.
// Table.GenerateByPage will keep asking for more pages until we return null.
GetAllPagesByNextLink = (url as text) as table =>
Table.GenerateByPage((previous) => 
let
// if previous is null, then this is our first page of data
nextLink = if (previous = null) then url else Value.Metadata(previous)[NextLink]?,
// if NextLink was set to null by the previous call, we know we have no more data
page = if (nextLink <> null) then GetPage(nextLink) else null
in
page
);

GetPage = (url as text) as table =>
let
response = Web.Contents(url, [ Headers = DefaultRequestHeaders ]), 
body = Json.Document(response),
nextLink = GetNextLink(body),
data = Table.FromRecords(body[data])
in
data meta [NextLink = nextLink];

// In this implementation, 'response' will be the parsed body of the response after the call to Json.Document.
// Look for the '@odata.nextLink' field and simply return null if it doesn't exist.
GetNextLink = (response) as nullable text => Record.FieldOrDefault(response, "@odata.nextLink");

Value.WaitFor = (producer as function, interval as function, optional count as number) as any =>
let
list = List.Generate(
() => {0, null},
(state) => state{0} <> null and (count = null or state{0} < count),
(state) => if state{1} <> null then {null, state{1}} else {1 + state{0}, Function.InvokeAfter(() => producer(state{0}), interval(state{0}))},
(state) => state{1})
in
List.Last(list);

I found this but i don't know if i can use this and where i need to write this :

raw = Web.Contents(...),
columns = raw[columns],
columnTitles = List.Transform(columns, each [title]),
columnTitlesWithRowNumber = List.InsertRange(columnTitles, 0, {"RowNumber"}),

RowAsList = (row) =>
let
listOfCells = row[cells],
cellValuesList = List.Transform(listOfCells, each if Record.HasFields(_, "value") then [value]
else null),
rowNumberFirst = List.InsertRange(cellValuesList, 0, {row[rowNumber]})
in
rowNumberFirst,

listOfRows = List.Transform(raw[rows], each RowAsList(_)),
result = Table.FromRows(listOfRows, columnTitlesWithRowNumber)

Thanks you for help and see you soon 🙂

[Wanted]

I understand your answer but I can't because I have to do it in my connector before the end of its process because after all the line is in error and I can't modify them anymore through this way.

[lbendlin]

Yes, I was referring to the code inside your custom connector. You need to apply these methods there.

[lbendlin]

In Power Query you have multiple ways to handle these situations. For example  try ... otherwise ..., or appending a question mark to a column name, or using Value.Is()