How to use Select to simplify Create-HTML-Table and Create-CSV in MicrosoftFlow

/
pexels-select-sort-complexity.jpeg

This is a blog post about how using Data Operations - Select in Microsoft Flow can dramatically simplify Create HTML Table or Create CSV.

This is mostly inspired by a conversation with @gmfer that I feel needs a bit more explanation for anyone that's curious.

Technically, this is also a blog post about how these three operations are fundamentally the same thing.  They iterate over an array and perform map/reduce.  As a developer as well as a language nut, I love map/reduce.

Plan

  • What is the problem with Create HTML Table (or Create CSV)
  • Why the fix is Select
  • The underlying language expression holds the answers to all the questions

The Data Operations actions

The problem

The problem is hard to explain but becomes very apparent as soon as you try to use Create HTML or Create CSV.

My example list is my Xbox Achievements - I'm pulling them into a HTML Table and a CSV File

html-simple.png

Problem 1 - Sometimes bugs with connectors (like SharePoint)
Problem 2 - Expression Editor does not appear - forcing us to write expression without help (use a compose action as a workaround)

Problem 3 - decide to define headers it wipes out existing columns.

This way is quite tricky, but we can get to the result, which looks great.  The downside is we have to know the expressions.

 

The Select

Let's see how we do this with Data Operation - Select, and why it is easier because we can use the Expression editor

The expressions are moved into a previous Select operation, that makes create HTML action (or create CSV action) very simple - just use the defaults.

 

The language

For the curious, the root of all three action lies in the expression.

Create HTML Table

"Create_HTML_table": {
    "type": "Table",
    "inputs": {
        "from": "@body('Get_items')?['value']",
        "format": "HTML",
        "columns": [
            {
                "header": "Title",
                "value": "@item()?['Title']"
            },
            {
                "header": "State",
                "value": "@item()?['progressState']"
            },
            {
                "header": "Unlocked",
                "value": "@formatDateTime(item()?['timeUnlocked'], 'yyyy-MM-dd')"
            }
        ]
    }
}

Create CSV Table

"Create_CSV_table": {
    "type": "Table",
    "inputs": {
        "from": "@body('Get_items')?['value']",
        "format": "CSV",
        "columns": [
            {
                "header": "Title",
                "value": "@item()?['Title']"
            },
            {
                "header": "State",
                "value": "@item()?['progressState']"
            },
            {
                "header": "Unlocked",
                "value": "@formatDateTime(item()?['timeUnlocked'], 'yyyy-MM-dd')"
            }
        ]
    }
}

Create CSV table and HTML table are the same.  If we don't want headers, the JSON changes to:

        "columns": [
            {
                "value": "@item()?['Title']"
            },
            {
                "value": "@item()?['progressState']"
            },
            {
                "value": "@formatDateTime(item()?['timeUnlocked'], 'yyyy-MM-dd')"
            }
        ]

Select and Create Table (with automatic columns) look like this

"Select": {
    "type": "Select",
    "inputs": {
        "from": "@body('Get_items')?['value']",
        "select": {
            "Title": "@item()?['Title']",
            "State": "@item()?['progressState']",
            "Unlocked": "@formatDateTime(item()?['timeUnlocked'], 'yyyy-MM-dd')"
        }
    }
},
"Create_HTML_table_2": {
    "runAfter": {
        "Select": [
            "Succeeded"
        ]
    },
    "type": "Table",
    "inputs": {
        "from": "@body('Select')",
        "format": "HTML"
    }
}

So if we look at the part for the field mappings being defined in Select as well as how they are listed in Create Table - Columns, the syntax of each item property expression is identical.

In a way, understanding the select expression helps us know what to put back inside the Create CSV/HTML Table actions.

Why can't we just use the same Select UI for Create CSV/HTML Table

Ultimately, the UI for Select allows mapping not only to fields but also to complex JSON objects, and goes far further than the header/value fields that Create CSV/HTML table actions need.  So while I love the Select action's UI since it works well with expression editor, that UI can't be used to replace the Create CSV/HTML table actions UI directly.

 

Summary

  • We looked at how to use Create CSV/HTML Table with expressions (this works, but is hard)
  • I recommend using Select to pick out the fields and the name of the field that you need, and then use automatic columns
  • And we looked at the underlying expressions and see these three are nearly identical.  They all do map reduce of a complex collection of objects and project them into a simpler (or different formatted) result.
  • Thank you @gmfer for the discussion and ultimately the motivation to write all this down.