# Table functions | Function | Description | Example Expression | Example Result | | ------------------------------------- | --------------------------------------------------------------------------------------------------------- | ----------------------------------------------- | --------------------------- | | `Count(table)` | Returns the number of rows in a table. | `Count([col: "A"] & [col: "B"])` | 2 | | `Any(table)` | Returns a truth (Boolean) value indicating whether the table has any rows. | `Any([col: "A"] & [col: "B"])` | TRUE | | `Skip(table, n)` | Returns a table where first *n* rows have been excluded. | `Skip([col: "A"] & [col: "B"] & [col: "C"], 1)` | <\[col: "B"] & \[col: "C"]> | | `Take(table, n)` | Returns a table where first *n* rows are included in result. | `Take([col: "A"] & [col: "B"] & [col: "C"], 2)` | <\[col: "A"] & \[col: "B"]> | | `First(table)` | Returns a record representing the first row of a table. | `First([col: "A"] & [col: "B"])` | <\[col: "A"] as Record> | | `Sum(table.column)` | Returns the summarized value of the specified table column. | `Sum([col: "4"] & [col: "7"])` | 11 | | `All(table, predicate)` | Returns a Boolean value indicating whether the predicate expression holds tree for all rows in the table. | `All([a: 1] & [a: 2], {a > 1})` | false | | `Eval(table)` | Forces evaluation of a lazy table expression. Rarely necessary. | Eval(numbers where x < 0) | \ | | `CsvFill(prototype, data, separator)` | Creates a Flow table from a string value containing CSV data. | *See below* | *See below* | | `XmlFill(prototype, data, rootXpath)` | Creates a Flow table from a string value containing XML data | *See below* | *See below* | ### **The WHERE keyword** You can use the *WHERE* keyword to create a filtered version of a table variable. Since this filtering happens locally on the device where the Flow client is run, it is not suitable for large amounts of data. The *WHERE* keyword takes a table on the left hand side and a logical expression (with the scope of the table's columns) on the right hand side. Since the *WHERE* keyword returns a new table, you can nest it in any table function, such as `First`, `Any`, `Count`, etc. ```fsharp let orders = [id: 1, isConfirmed: 0, plannedDate: Now()] & [id: 2, isConfirmed: 1, plannedDate: Now() + 1] & [id: 3, isConfirmed: 0, plannedDate: Now() + 2]; let futureOrders = orders where isConfirmed and plannedDate > Now(); let nextOrderToProcess = first(orders where isConfirmed); let numberOfOrder = count(orders where isConfirmed); ``` ### **The ORDER BY keyword** *ORDER BY* sorts a table by one column and returns a new sorted table. As default the order is asending but by adding the keyword *DESC* the order can be changed to descending (*ASC* can be added for explicitly when sorting ascending). Only simple types can used for sorting. ```fsharp let items = [id: 1, price: 10, name: 'Soda'] & [id: 2, price: 100, name: 'Burger'] & [id: 3, price: 50, name: 'Fries']; let cheapItem = First(items order by price); let expensiveItem = First(items order by price desc); ``` ### **The SUM function** The Sum function calculates the sum of the values in a numeric column in a table. ```fsharp let projectedProfit = sum((orders where isConfirmed).value) - sum(expenses.cost); ``` ### **The JOIN function** The Join function concatenates all values of a table column into a single textual value with a given separator. In the example below, the **orderNo** column of the **orders** table is aggregated into a comma-separated string in the format `, , ` etc. ```fsharp let orderNumbers = join(orders.orderNo, ', '); ``` ### **The MAP-AS construct** The Map-As construct is used to create a new table variable based on an existing table variable, changing the column names and adding or removing new columns. The syntax of the map-as construct is `MAP AS `. The record literal to the right of the AS keyword has the scope of the table. The example below shows an orderLines table being transformed, keeping the salesPart column but adding a new column called cost, which is calculated using the price and discountPercent columns of the orderLines table. ```fsharp let newOrderLines = map orderLines as [salesPart, cost: price * discount]; ``` ### **CSV data** The CsvFill function can be used to parse CSV data into a Flow table. In order to do this, the function needs a *prototype table*, which is an empty table specifying the columns expected in the CSV data. If not all columns are present in the CSV data, the function will fail. Given the following semicolon-separated CSV: ```fsharp name;lastName;age Bob;Smith;50 Alice;Smith;60 Per;Lagerqvist;40 ``` ...you can use the CsvFill function as such: ```fsharp let prototype = table(name, lastName, age); let result = CsvFill(prototype, csvData, ";"); // the 'result' variable will be a table containing the data from the CSV block ``` ### XML data Similarly, the XmlFill function can be used to parse simple XML data into a Flow table. In order to do this, the function needs a *prototype table*, which is an empty table specifying the columns expected in the XML data. If not all columns are present in the XML data, the function will fail. The third argument of the XmlFill function can be used to specify an [XPath expression](https://www.w3schools.com/xml/xpath_intro.asp) pointing out the root element under which each row is found. Given the following XML: ```markup cat dog ``` ...you can use the XMLFill function as such: ```fsharp let prototype = table(name); let result = XmlFill(prototype, xmlData, ""); // The 'result' variable will contain two rows corresponding to the // elements in the XML above. ```