Advanced Data Operations (Preview)
A suite of advanced operations designed to assist in the transformation of bulk datasets.
This connector is available in the following products and regions:
| Service | Class | Regions |
|---|---|---|
| Logic Apps | Standard | All Logic Apps regions except the following: - Azure Government regions - Azure China regions - US Department of Defense (DoD) |
| Power Automate | Premium | All Power Automate regions except the following: - US Government (GCC) - US Government (GCC High) - China Cloud operated by 21Vianet - US Department of Defense (DoD) |
| Power Apps | Premium | All Power Apps regions except the following: - US Government (GCC) - US Government (GCC High) - China Cloud operated by 21Vianet - US Department of Defense (DoD) |
| Contact | |
|---|---|
| Name | State Solutions Support |
| URL | https://www.statesolutions.com.au/contact-us |
| support@statesolutions.com.au |
| Connector Metadata | |
|---|---|
| Publisher | State Solutions |
| Website | https://www.statesolutions.com.au |
| Privacy Policy | https://www.statesolutions.com.au/privacy-policy |
| Categories | Data |
The operations offered as a part of this service provides data & integration engineers with the ability to transform bulk datasets from one structure into a another.
Along with the core operations, the framework has custom functions built in which extend the functionality even further again. These added custom functions provide field specific level extensibility that can be applied against the dataset being transformed.
The operations within the framework helps to reduce overhead, such as looping, overuse of inline expressions and other basic data operations, within the developed flows. This results in a more readable and more supportable end product for your organisation.
Such operations, including but not limited to, are aggregation, joining (cartesian, left and inner joins), distinct selections, data format conversion (e.g. CSV, text, JSON and Parquet), the flattening of highly nested complex objects, generic transformation, regex operations and an expert operation where a fully fledged SQL statement can be executed over the data.
All of this made possible without a single piece of infrastructure required to be invoked from your own organisations perspective.
Prerequisites
You'll need to sign up for a subscription.
One can be obtained by signing up to a paid plan or by activating a trial subscription. Details can be found on our website under the pricing page ... http://www.statesolutions.com.au/pricing/
Tha API key you are supplied with will need to be added to the header of each call executed over our service.
How to get credentials
As per the previous section, navigate to our pricing page and sign up for the plan that you feel suits your organisation best.
If you sign up for a paid subscription, the process will direct you to enter details relating to yourself and your organisation. You will then be asked to provide credit card information so the monthly billing cycle can charge you accordingly.
If you sign up for a trial subscription, you will be asked for nothing more than your name and email address. Your trial subscription limits you to 100 calls with no time limit on when those 100 calls need to be used up.
Your details (email address and product purchased) are used when invoking services on our side to track any and all usage that is required for quota limiting (please note that not all plans have a quota limit).
Get started with your connector
Provide users with a step-by-step process for getting started with your connector. This is where you should highlight common use cases, such as your expected popular triggers and actions, and how they can help in automation scenarios. Include images where possible.
After you've received your subscription and API key, you'll be ready to execute your first call.
Overview - http://www.statesolutions.com.au/overview/
The overview section of of our website explains the basics and most importantly, details the main endpoint that's used to make each API call.
Common Parameters - http://www.statesolutions.com.au/common-parameters/
Each API has a set of common parameters that can be used to define specific schema information, filter and sort response data and set advanced options. There is no doubt that you will need at least one of these options when working with our framework so it's important to understand how they work together.
Custom Function - http://www.statesolutions.com.au/custom-functions/
Not all operations support the use of custom functions but for those that do, you'll want to be across that which is possible given it will give you an immense amount of flexibility when transforming your data.
From there, you'll need to familiarise yourself with each of the different operations that form the basis of the framework.
Documentation from each API can be found on our website.
Aggregate- Aggregate one or more properties by a grouped set of fields within a JSON array dataset.Cartesian Join- Perform a Cartesian join over two or more datasets.Concatenate- Concatenate a field from an array of objects into a single string output.Concatenate (Simple)- Concatenate an array of values into a delimited string.CSV to JSON- Transform a comma separated table of data into an array of JSON objects.Distinct- Select a set of distinct property values from an array of objects.Distinct (Simple)- Provide a distinct set of values from an array.Expert- Execute a SQL statement over one or more supplied datasets.Flatten Object Array- Flatten an array of complex, multi-level objects into an array of single level JSON objects.Get Data Schema- Get the schema (i.e. properties and their associated types) from an array of objects.Join- Join exactly two datasets using a left or inner join technique.JSON to Text- Transform a JSON array of objects into a tab delimited text format.JSON to CSV- Transform a JSON array of objects into a comma separated table format.Parquet to JSON- Transform a base64 encoded parquet dataset into an array of JSON objects.Regex Matches- Produce a set of matches for a string using a regular expression pattern.Split- Split each item of an array into one or more new items by a defined set of criteria.Text to JSON- Transform a tab delimited textual dataset into an array of JSON objects.Transform- Transform a set of properties in a dataset using any of the available custom transformation functions provided by the framework.
Known issues and limitations
The service is hosted on Azure cloud infrastructure and like any computing based service is limited when it comes to CPU and memory usage. Therefore, the primary limitation our connector faces is those which fall in line with system resources.
Testing has revealed that payloads should not exceed around 30mb's (+/-) in size for each call being made. This limitation may be resolved in the future but as of right now, the limitation exists and needs to be dealt with from the calling side of the equation.
The way to work around this is to split up your payloads into logical chunks and process those one by one. An example of logical chunks can be taken from financial data, it may be best to split the payloads up by fiscal period, fiscal year or some kind of logical organisational attribute.
Common errors and remedies
Error 401 : The quota limit for this subscription has been exceeded OR your subscription is not permitted to use this operation.
You have exceeded your quota for the billing period. You'll need to upgrade your subscription or wait until the next billing period. Alternatively, if your quota has not been exceeded, the operation you are attempting to call is not supported by the subscription level you've paid for.
FAQ
You can find an FAQ page on our website ... http://www.statesolutions.com.au/faq
Creating a connection
The connector supports the following authentication types:
| Default | Parameters for creating connection. | All regions | Not shareable |
Default
Applicable: All regions
Parameters for creating connection.
This is not shareable connection. If the power app is shared with another user, another user will be prompted to create new connection explicitly.
| Name | Type | Description | Required |
|---|---|---|---|
| Advanced Data Operations API Key | securestring | API Key | True |
Throttling Limits
| Name | Calls | Renewal Period |
|---|---|---|
| API calls per connection | 100 | 60 seconds |
Actions
| Aggregate |
Aggregate one or more properties by a grouped set of fields within a JSON array dataset. |
| Cartesian Join |
Perform a Cartesian join over two or more datasets. |
| Concatenate |
Concatenate a field from an array of objects into a single string output. |
| Concatenate (Simple) |
Concatenate an array of values into a delimited string. |
| CSV to Json |
Transform a comma separated table of data into an array of JSON objects. |
| Distinct |
Select a set of distinct property values from an array of objects. |
| Distinct (Simple) |
Provide a distinct set of values from an array. |
| Expert |
Execute a SQL statement over one or more supplied datasets. |
| Flatten Object Array |
Flatten an array of complex, multi-level objects into an array of single JSON objects. |
| Get Data Schema |
Get the schema (i.e. properties and their associated types) from an array of objects. |
| Join |
Join exactly two datasets using a left or inner join technique. |
| Json to CSV |
Transform a JSON array of objects into a comma separated table format. |
| Json to Table |
Transform a JSON structure into a tabular format. |
| Json to Text |
Transform a JSON array of objects into a tab delimited text format. |
| Parquet to Json |
Transform a base64 encoded parquet dataset into an array of JSON objects. |
| Regex Matches |
Produce a set of matches for a string using a regular expression pattern. |
| Split |
Split all objects contained within an array by a defined criteria. |
| Text to Json |
Transform a tab delimited textual dataset into an array of JSON objects. |
| Transform |
Transform a set of properties in a dataset using any of the available custom transformation functions. |
Aggregate
Aggregate one or more properties by a grouped set of fields within a JSON array dataset.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Operation
|
aggregationType | True | string |
The aggregation type. |
|
Aggregate By
|
aggregateBy | string |
The fields to aggregate by (i.e. the non-numeric properties). |
|
|
Aggregate On
|
aggregateOn | string |
The fields to aggregate on (i.e. the numeric properties). |
|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Cartesian Join
Perform a Cartesian join over two or more datasets.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Concatenate
Concatenate a field from an array of objects into a single string output.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Field
|
field | True | string |
Field from the source data set to concatenate. |
|
Separator
|
separator | string |
The string used to delimit the resulting concatenated string. |
|
|
Ignore Empty
|
ignoreEmpty | True | boolean |
Ignore blank/null strings when concatenating. |
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
- response
- string
Concatenate (Simple)
Concatenate an array of values into a delimited string.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Separator
|
separator | string |
The string used to delimit the resulting concatenated string. |
|
|
Ignore Empty
|
ignoreEmpty | boolean |
Ignore blank/null strings when concatenating. |
|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Sort Order
|
sortOrder | string |
Sorting direction (i.e. ASC or DESC). |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string | ||
|
Data
|
data | string |
Array of strings or numeric values. |
Returns
- response
- string
CSV to Json
Transform a comma separated table of data into an array of JSON objects.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Header Row
|
headerRow | boolean |
Contains header row |
|
|
Row Separator
|
rowSeparator | string |
The character(s) separating each row, default = \n |
|
|
Escape Character
|
escapeCharacter | string |
The escape character to use when splitting each field, default is double quotes. |
|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string | ||
|
Data
|
data | True | string |
Delimited string table |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Distinct
Select a set of distinct property values from an array of objects.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Fields
|
Fields | string | ||
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Distinct (Simple)
Provide a distinct set of values from an array.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Field
|
field | True | string |
Field to perform the distinct on. |
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Sort Order
|
sortOrder | string |
Sorting direction (i.e. ASC or DESC). |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Expert
Execute a SQL statement over one or more supplied datasets.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Query String
|
query | True | string |
Fully functioning SELECT statement that refers to and processes the incoming data set. |
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Flatten Object Array
Flatten an array of complex, multi-level objects into an array of single JSON objects.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Delimiter
|
delimiter | True | string |
The string used to delimit each property from the different levels in the object hierarchy. |
|
Balanced Output
|
balancedOutput | True | boolean |
If TRUE, each item in the result set will have the same set of properties, therefore, making it balanced. Filter and Sort Order only work if this is set to TRUE. |
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Get Data Schema
Get the schema (i.e. properties and their associated types) from an array of objects.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Join
Join exactly two datasets using a left or inner join technique.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Join Type
|
joinType | True | string |
The join operation to perform. |
|
Join Fields
|
Join Fields | string | ||
|
Fields
|
Fields | string | ||
|
Force Fully Qualified Field Names
|
forceFullyQualifiedFieldNames | boolean |
Force all field names in the resulting dataset to be prefixed with the originating dataset name. |
|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Json to CSV
Transform a JSON array of objects into a comma separated table format.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Header Row
|
headerRow | boolean |
Contains header row |
|
|
Row Separator
|
rowSeparator | string |
The character(s) separating each row, default = \n |
|
|
Escape Character
|
escapeCharacter | string |
The escape character for values that contain the field separator value. |
|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
- response
- string
Json to Table
Transform a JSON structure into a tabular format.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Path
|
path | string |
JToken path used for initial selection (default = $) |
|
|
Balanced Output
|
balancedOutput | boolean |
If TRUE, each item in the result set will have the same set of properties, therefore, making it balanced. |
|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Json to Text
Transform a JSON array of objects into a tab delimited text format.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Header Row
|
headerRow | boolean |
Contains header row |
|
|
Row Separator
|
rowSeparator | string |
The character(s) separating each row, default = \n |
|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
- response
- string
Parquet to Json
Transform a base64 encoded parquet dataset into an array of JSON objects.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string | ||
|
Data
|
data | True | string |
Base64 encoded string containing contents of a parquet data file. |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Regex Matches
Produce a set of matches for a string using a regular expression pattern.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Pattern
|
pattern | True | string |
Regular expression pattern. |
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string | ||
|
Data
|
data | True | string |
Text to perform the regex pattern over |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of string |
Split
Split all objects contained within an array by a defined criteria.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Text to Json
Transform a tab delimited textual dataset into an array of JSON objects.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Header Row
|
headerRow | boolean |
Contains header row |
|
|
Row Separator
|
rowSeparator | string |
The character(s) separating each row, default = \n |
|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string | ||
|
Data
|
data | True | string |
Delimited string table |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Transform
Transform a set of properties in a dataset using any of the available custom transformation functions.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Preserve All Properties
|
preserveAllProperties | boolean |
If TRUE, all properties not specifically called out in the transformations property will be preserved in the resulting dataset. |
|
|
Filter
|
filter | string |
String based WHERE clause used to filter the returned result set. Relies on Balanced Output being set to TRUE. |
|
|
Culture Name
|
cultureName | string |
Allows the calling application to change the culture so date parsing, etc. produces the correct result. |
|
|
Boolean Properties
|
Boolean Properties | string |
Returns
| Name | Path | Type | Description |
|---|---|---|---|
|
|
array of object | ||
|
items
|
object |
Definitions
string
This is the basic data type 'string'.