Power Query functions

03 Feb, 2022

This is a "repository" of useful functions I've written/come across. Some are better for Excel, and some are better for Power BI, all are usable in both.

Last update: 28^th August, 2024

Contents

Utility functions

Default function parameter

Power Query doesn't have a way of defining default values for function parameters. This is something that I look for, especially when defining functions with optional parameters.

This function handles cases when optional parameters are supplied as null and need a default value.

Code

(value as any, default as any) as any => 
    if value = null then default else value

Switch

Every time I see a long train of if... then... else if... then... else if... I say a silent prayer for a switch keyword in Power Query. Until that prayer is answered, I'll have to make do with this one that I wrote, inspired by Power Apps and Excel.

This is just "syntactic sugar". It merely makes it look like you're using a switch function. I would guess that you'll take a performance hit if you used this instead of a normal if...then...else... chain.

All that being said... ... ... here it is. I used it in an each, but you can use it however you like.

Code

(value as any, criteria as list, default as nullable text) as any => 
    try
        let
            result = List.Select(
                criteria,
                each _{0} = value
            ){0}{1}
        in
            result
    otherwise
        null

Example

colours = Table.AddColumn(
    riskRankings,
    "Colour", 
    each 
        switch(
            [Risk Ranking], 
            {
                {"Critical", "red"},
                {"High", "orange"},
                {"Moderate", "yellow"},
                {"Low", "green"}
            }, 
            null
        )
)

Custom sorting function

Usually when using a sorting function like List.Sort, I'll typically use Order.Ascending or Order.Descending as the ordering function. Nice and easy!

However, sometimes you want something a bit more sophisticated than that. The go-to might be to create a custom column by Table.AddColumn, make a number out of that, and sort by that column instead.

If you're dealing with a text column, this function orderTextBy might help you do all that in one step.

Check out the examples, and pay special attention to the symtax.

Code

orderTextBy = (textOperations as function, direction as text) as function => 
    let 
        before = -1, 
        after = 1,
        same = 0
    in 
        if direction = "asc" then
            (a as any, b as any) as number => if textOperations(a) < textOperations(b) then before else if textOperations(a) > textOperations(b) then after else same
        else if direction = "desc" then 
            (a as any, b as any) as number => if textOperations(a) > textOperations(b) then before else if textOperations(a) < textOperations(b) then after else same
        else
            error [
                Reason = "Wrong direction supplied", 
                Message = "The sort direction has to be ""asc"" or ""desc"""
            ]

Example 1: sort by text length

Note below that `Text.Length` is provided as a function without calling it (i.e. we didn't `Text.Length(_)`, the brackets were left out).

let
    songs = {
        "Honeymoon Avenue",
        "Baby I",
        "Right There",
        "Tattooed Heart",
        "Lovin' It",
        "Piano",
        "Daydreamin'",
        "The Way",
        "You'll Never Know",
        "Almost Is Never Enough",
        "Popular Song",
        "Better Left Unsaid"
    },

    sorted = List.Sort(
        songs, 
        orderTextBy(Text.Length, "asc")
    )
in
    sorted

This gives the list, sorted by the text length (number of characters):

List
Piano
Baby I
The Way
Lovin' It
Daydreamin'
Right There
Popular Song
Tattooed Heart
Honeymoon Avenue
You'll Never Know
Better Left Unsaid
Almost Is Never Enough

Example 2: sort by number of words

In this example, we're compounding two functions into one. The function orderTextBy only takes a single function in the first argument, so you'll have to define a new function, or wrap it up in an anonymous function (see alternative syntax below).

let
    songs = {
        "Honeymoon Avenue",
        "Baby I",
        "Right There",
        "Tattooed Heart",
        "Lovin' It",
        "Piano",
        "Daydreamin'",
        "The Way",
        "You'll Never Know",
        "Almost Is Never Enough",
        "Popular Song",
        "Better Left Unsaid"
    },

    wordCount = (_) => List.Count(Text.Split(_, " ")),

    sorted = List.Sort(
        songs, 
        orderTextBy(wordCount, "asc")
    )

    /*
        /// ALTERNATIVE SYNTAX ///
        sorted = List.Sort(
            songs, 
            orderTextBy((_) => List.Count(Text.Split(_, " ")), "asc")
        )
     */
in
    sorted

This gives the list, sorted by the number of words:

List
Daydreamin'
Piano
Honeymoon Avenue
Popular Song
The Way
Right There
Baby I
Lovin' It
Tattooed Heart
Better Left Unsaid
You'll Never Know
Almost Is Never Enough

Table functions

Adding multiple table columns

Sometimes I wish that Power Query had the nice AddColumns function like Power Apps has. The default Table.AddColumn() can only add you one column at a once, and adding a whole set of columns in sequence leaves you with ugly code.

Thus, I've gone ahead and made my own tableAddColumns() function.

It takes two arguments: the source table you're adding onto, and a list of column definitions in a list-of-lists type. The columns to add are composed of a name and a generator function (which you can define using the each keyword). The generator functions can even make use of column references - see example 1 below. Type is an optional parameter.

Code


tableAddColumns = (source as table, columnsToAdd as list) as table => 
    List.Accumulate(
        columnsToAdd,
        source,
        (base as table, thisColumn as list) => 
            Table.AddColumn(
                base, 
                thisColumn{0},
                thisColumn{1},
                try thisColumn{2} otherwise null
            )
        )

Example 1


let 
    source = #table(
        {"col1", "col2"},
        {
            {1,1},
            {2,2}
        }
    ),
    columnsToAdd = {
        {"col3", each [col1] + 5},
        {"col4", each [col3] + [col2]}
    },
    newTable = Table.AddColumns(source, columnsToAdd)
in
    newTable

You end up with this:

col1	col2	col3	col4
1	1	6	7
2	2	7	8

Add column to front

By default, Table.AddColumn() adds the column to back (far right) of the table.

What if you want to add it right to the front instead, and don't want to rearrange by hand? This does exactly that.

Code


(source as table, newColumnName as text, columnFunction as function, optional newColumnType as nullable type) as table => 
        let
            addColumn = Table.AddColumn(source, newColumnName, columnFunction, newColumnType),
            newColumnFirst = {newColumnName} & Table.ColumnNames(source),
            reorderColumns = Table.ReorderColumns(
                addColumn, 
                newColumnFirst
            )
        in
            reorderColumns

Add total row

I needed to automate the creation of a table with a total row. This is trivial to do in normal Excel - you don't even have to do anything clever, you just have to check the "Total Row" checkbox in the Table tab in the ribbon!

However, I wanted this automated table to live in Power BI. It's used as part of a report, and I wanted everything to be right there when it comes time to do up the report.

Code


(sourceTable as table, totalRowHeading as text) as table =>
let
    source = Table.Buffer(sourceTable),
    columnsNames = Table.ColumnNames(source),
    quantityColumnNames = List.RemoveFirstN(columnsNames, 1),
    totalRowHeadingCell = {
        totalRowHeading as text
    },
    totalRowQuantityCells = List.Transform(
        quantityColumnNames,
        each List.Sum(
            Table.Column(source, _) as list
        ) as number
    ) as list,
    totalRow = #table(
        columnsNames, 
        {
            List.Combine(
                {
                    totalRowHeadingCell, 
                    totalRowQuantityCells
                }
            )
        }
    ),
    combined = Table.Combine(
        {
            source, 
            totalRow
        }
    )
in
    combined

Example

This function only works with a table with the following structure:

Column 1	Quantity 1	Quantity 2	Quantity 3	Quantity 4
Heading 1	1	2	3	4
Heading 2	3	6	9	12

That is, headings in the first column, numbers in the rest of the columns. It will break otherwise.

Calling addTotalRow(sourceTable, "Total") will give you this:

Column 1	Quantity 1	Quantity 2	Quantity 3	Quantity 4
Heading 1	1	2	3	4
Heading 2	3	6	9	12
Total	4	8	12	16

Add an ID column on the far left

Adding an index column via the GUI is fine, but it will add it as a new column on the far right of the table, and name it "Index". Much of the time, I want it on the far left of my table, and have it names "ID". That's what this function does.

Code

 
(source as table) as table => 
    let
        columns = Table.ColumnNames(source) as list,
        addIndex = Table.AddIndexColumn(source, "ID", 1, 1, Int64.Type),
        rearrangeIDToLeft = Table.ReorderColumns(
            addIndex,
            List.Combine(
                {
                    {"ID"}, 
                    columns
                }
            )
        )
    in
        rearrangeIDToLeft

Base Name for File

When working with file folders, the [Name] column is always the .FullName of the file, including the file extension. In order to get just the .BaseName, you could split at the character ".", but what if the filename includes a "."? Or more than one? I find that a convenient way to remove the extension is by accessing file[Extension], splitting there, and picking up the first element.

(file as record) as text => let extension = file[Extension], basename = Text.Split(file[Name], extension){0} in basename

Count If

Sometimes, you just want a CountIf(), you know?

The condition needs to start with each.

Code


(source as table, condition as function) as number =>
    Table.RowCount(
        Table.SelectRows(
            source,
            condition
        )
    )

Describe table

Sometimes when working on a query that references a big table, it's inconvenient to go back and forth between the query editor and the table you're referencing. Particularly, sometimes all I want to know is how a particular column is spelled exactly, or what data type it is, or what are the possible values of that column.

Power Query natively has the Table.Schema() function, which is very detailed. I use Table.Schema as a base, clean up the columns, and add in a column that holds lists of possible values.

Code


(sourceTable as table) as table => 
    let
        source = Table.Buffer(sourceTable),
        schema = Table.Schema(source),
        #"Removed Other Columns" = Table.SelectColumns(schema,{"Name", "Position", "Kind"}),
        sort = Table.Sort(#"Removed Other Columns",{{"Name", Order.Ascending}}),
        possibleValues = Table.AddColumn(
            sort,
            "Possible Values", 
            each List.Distinct(
                Table.Column(source, [Name])
            )
        )
    in
        possibleValues

Expand record, all fields

This is for expanding a table column that holds Records. All of the Fields in the Record are expanded out into their own column.

The problem I came across was that the default Table.ExpandRecordColumn function takes a discrete List as the list of columns to expand. If you're still working on the script, and for some reason the Record was changed upstream in any way (field names were changed, or fields were added, or fields were taken away), Table.ExpandRecordColumn would miss those changes or throw an error, and you'll have to manually update the field list.

Using this function, expanding all fields would always be kept in sync with the definition of the record.

Code


(source as table, columnName as text, optional keepOriginalColumnName as nullable logical) as table => 
    let
        column = Table.Column(source, columnName) as list, 
        firstRecord = column{0} as record,
        recordFields = Record.FieldNames(firstRecord) as list,
        recordFieldsWithOriginalColumnName = List.Transform(
            recordFields, 
            each columnName & "." & _
        ),
        expand = Table.ExpandRecordColumn(
            source, 
            columnName, 
            recordFields, 
            if keepOriginalColumnName then recordFieldsWithOriginalColumnName 
            else recordFields
        )
    in 
        expand

Multiple merge columns

This is for collecting together multiple sets of merging columns.

The input is of this form:

  {
      [ columns = {}, newName = ""],
      [ columns = {}, newName = ""],
      [ columns = {}, newName = ""]
  }

Code


(source as table, columnsToMerge as list) as table =>
    List.Accumulate(
        columnsToMerge,
        source,
        (base as table, theseColumns as record) =>
            Table.CombineColumns(
                base,
                theseColumns[columns],
                Combiner.CombineTextByDelimiter("", QuoteStyle.None),
                theseColumns[newName]
            )
        )

Profile (including uniqueness)

This is a quick way to get a profile of a table, and also check for uniqueness across every column.

Thanks to Chris Webb's blog for showing me how to use the second parameter.

Code


  Profile = Table.Profile(
        Source,
        {
            {
               "Unique", 
                each true, 
                List.IsDistinct
            }
        }
    )

Remove default columns

When you import a CSV or similar, sometimes Power Query will bring in "blank" columns. These columns will all be named Column1, Column2, Column3, and so on.

This function strips out all of the columns that start with the string "Column", leaving you with only the columns that really mean to keep.

Additionally, this function will also trim any whitespace around the column names.

Code


(source as table) as table =>
    let
        columns = Table.ColumnNames(source),
        columnsToKeep = List.Select(columns, each not Text.StartsWith(_, "Column")),
        filterOutDefaultColumns = Table.SelectColumns(source, columnsToKeep),
        newColumns = Table.ColumnNames(filterOutDefaultColumns),
        // also trim column names
        trimColumnNames = Table.RenameColumns(
            filterOutDefaultColumns,
            List.Transform(
                newColumns,
                each {_, Text.Trim(_)}
            )
        )
    in
        trimColumnNames

Remove empty columns

Sometime you get tables where the columns are totally null. This will filter them out.

Code


(source as table) as table =>
    let
        columnNames = Table.ColumnNames(source),
        selectColumns = Table.SelectColumns(
            source,
            List.Select(
                columnNames,
                each List.MatchesAny(Table.Column(source, _), each _ <> null)
            )
        )
    in
        selectColumns

Code


let
    Source = (source as table) => 
    let
        source = Table.Buffer(source),
    columns = List.Transform(
        Table.ColumnNames(source),
        each [
            Column Name = _, 
            #"Non-Null Count" = List.Count(
                List.Select(
                    Table.Column(source, _), 
                    each _ <> null
                )
            )
        ]
    ),

    nonNullColumns = List.Buffer(
        Table.FromRecords(
            List.Select(
                columns, 
                each [#"Non-Null Count"] <> 0
            )
        )[Column Name]
    ),

    removeNullColumns = Table.Buffer(
        Table.SelectColumns(
            source, 
            nonNullColumns
        )
    )
in
    removeNullColumns

in
Source

Replace multiple values

Code


(source as table, replaceValuePairs as list, replaceInColumns as list) as table =>
    List.Accumulate(
        replaceValuePairs,
        source,
        (base as table, thisReplaceValuePair as list) =>
            Table.ReplaceValue(
                base,
                thisReplaceValuePair{0},
                thisReplaceValuePair{1},
                Replacer.ReplaceText,
                replaceInColumns
            )
        )

Select columns "up to"

There are times where you may want to Table.SelectColumns() over a whole set of tables, but you can't be sure that all of those tables contain all of the columns you want to select for. For example, if one of the tables doesn't have one of the columns you're selecting, simply doing Table.SelectColumns() will throw an error.

This function only selects the columns from the target table that exist, and nothing more. It only select columns "up to" the target set.

Code


(source as table, columns as list) as table =>
    let
        allColumns = Table.ColumnNames(source),
        matchingColumns = List.Intersect({allColumns, columns}),
        selectColummns = Table.SelectColumns(
            source,
            matchingColumns
        )
    in
        selectColummns

Thin out table

Sometimes, I like to "thin out" tables. Maybe I'm trying to iterate quickly over a large data set, and I want to thin it out so that I'm working with a smaller data set. I do this by picking every n-th row in the table.

Code


(source as table, everynth as number) =>
    let
        index = Table.AddIndexColumn(source, "Index", 1, 1, Int64.Type),
        mod = Table.TransformColumns(
            index,
            { { "Index", each Number.Mod(_, everynth), Int64.Type } }
        ),
        filter = Table.SelectRows( mod, each [Index] = 0 ),
        cleanup = Table.RemoveColumns( filter, {"Index"} )
    in
        cleanup

Transform column if

Transform column, but with table-wide scope (i.e. can transform columns conditionally based on values in other columns). The second argument condition has table-wide scope.

Code


(source as table, condition as function, columnName as text, transformation as function) => 
    let
        listOfRecords = Table.TransformRows(
            source, 
            each Record.TransformFields(
                _,
                if condition(_) then {{columnName, transformation}} else {}
            )
        ) as list,
        listToTable = Table.FromRecords(
            listOfRecords
        ) as table
    in
        listToTable

List functions

Generate character list

Sometimes you need a list of all the characters in the alphabet, or all of the digits. Perhaps you could write them all out by hand and save it for later, that would probably just as handy as having a function for it. Nevertheless, here it is.

Code


(listOfRanges as list) as list => 
    /*
        special characters = {{32..47}, {58..64}, {91..96}, {123..127}}
        numbers = {48..57}
        uppercase = {65..90}
        lowercase = {97..122}
        whole alphabet = {{65..90}, {97..122}}
    */
    let
        makeCharacterArray = (numberArray as list) as list => 
            List.Transform(numberArray, each Character.FromNumber(_)),
        characterRanges = List.Transform(
            listOfRanges,
            each makeCharacterArray(_)
        )
    in
        List.Combine(characterRanges)

Sequence

Update forget all of this! This was before I learned about List.Numbers.

Inspired by Excel and Power Apps Sequence().

Code


(length as number, optional initial as number, optional increment as number) as list => 
    let 
        default = (value as any, default as any) => 
            if value = null then default else value,
        initial = default(initial, 1), 
        increment =  default(increment, 1)
    in
        List.Generate(
            () => initial, 
            each _ < initial + length,
            each _ + 1 as number
        )

Shuffle

This will shuffle a list in a random order. Note the custom sorting function.

Code

         
(source as list) as list =>
    List.Sort(
        source, 
        (a, b) => 
          // generate either -1 or 1
          Number.RoundAwayFromZero(Number.RandomBetween(-1, 1))
    )

List Remove Errors

There is Table.RemoveErrors, but not a corresponding function for Lists. One way is to convert the list to a single-column table, remove errors there, and then convert it back into a list.

Code


(source as list) as list =>
    let
        #"Converted to Table" = Table.FromList(source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Removed Errors" = Table.RemoveRowsWithErrors(#"Converted to Table", {"Column1"}),
        backToList = #"Removed Errors"[Column1]
    in
        backToList

Text functions

Contains All

Code


(string as text, substrings as list) => List.AllTrue(List.Transform(substrings, each Text.Contains(string, _)))

Decode URL ("percent-encoded")

Much of the time, I have percent-encoded URLs that I want to decode and revert back to "normal" text. The dictionary I use probably isn't so comprehensive, but you can add your own mapping if it's missing something. This is the first time that I confidently used List.Accumulate(), and truly understood how it worked. I've named the intermediate variables base and thisUrlEncodedSymbol in this context in place of state and current to make it more meaningful.

It would be more convenient to move the dictionary into its own module/query so that it can be referred to separately, instead of being rebuilt every time the function is called.

Code


dictionaryUrlEncoding = #table(
    {"char", "urlEncode"},
    {
        {" ", "%20"}, {"""", "%22"}, {"<", "%3C"}, {">", "%3E"}, {"#", "%23"}, {"%", "%25"}, {"{", "%7B"}, {"}", "%7D"}, {"|", "%7C"}, {"\\", "%5C"}, {"^", "%5E"}, {"~", "%7E"}, {"[", "%5B"}, {"]", "%5D"}, {"`", "%60"}, {"!", "%21"}, {"$", "%24"}, {"&", "%26"}, {"'", "%27"}, {"(", "%28"}, {")", "%29"}, {"*", "%2A"}, {"+", "%2B"}, {",", "%2C"}, {"/", "%2F"}, {":", "%3A"}, {";", "%3B"}, {"=", "%3D"}, {"?", "%3F"}, {"@", "%40"}
    }
),
decodeUrl = (rawUrl as text) as text =>
    let 
        dictionary = List.Buffer(dictionaryUrlEncoding[urlEncode])
    in
        List.Accumulate(
            dictionary as list,
            rawUrl as text,
            (base as text, thisUrlEncodedSymbol as text) as text =>
                Text.Replace(
                    base,
                    thisUrlEncodedSymbol,
                    dictionaryUrlEncoding{[urlEncode = thisUrlEncodedSymbol]}[char]
                )
        ) as text

Decode HTML character codes ("ampersand encoded")

Sometimes the HTML text that you get from a dataset contains ampersand-encoded characters. There are actually several sets of these, but I've made a dictionary and decoder that deals with the "numbered" codes (e.g. &) and the "named" codes (e.g. &).

Code


dictionaryHtmlCode = #table(
    {"char", "htmlCode", "htmlEntity"},
    { 
        { "!", "&#33;", "!" }, { "\", "&#34;", """ }, { "#", "&#35;", "#" }, { "%", "&#37;", "%" }, { "&", "&#38;", "&" }, { "'", "&#39;", "'" }, { "(", "&#40;", "(" }, { ")", "&#41;", ")" }, { "*", "&#42;", "*" }, { ",", "&#44;", "," }, { ".", "&#46;", "." }, { "/", "&#47;", "/" }, { ":", "&#58;", ":" }, { ";", "&#59;", ";" }, { "?", "&#63;", "?" }, { "@", "&#64;", "@" }, { "[", "&#91;", "[" }, { "\\", "amp;\", "\" }, { "]", "&#93;", "]" }, { "^", "&#94;", "^" }, { "_", "&#95;", "_" }, { "`", "&#96;", "`" }, { "{", "&#123;", "{" }, { "|", "&#124;", "|" }, { "}", "&#125;", "}" }, { "~", "&#126;", "˜" }, { " ", "&#160;", " " }, { "¡", "&#161;", "¡" }, { "¦", "&#166;", "¦" }, { "§", "&#167;", "§" }, { "¨", "&#168;", "¨" }, { "©", "&#169;", "©" }, { "ª", "&#170;", "ª" }, { "«", "&#171;", "«" }, { "¬", "&#172;", "¬" }, { "", "&#173;", "" }, { "®", "&#174;", "®" }, { "¯", "&#175;", "¯" }, { "²", "&#178;", "²" }, { "³", "&#179;", "³" }, { "´", "&#180;", "´" }, { "µ", "&#181;", "µ" }, { "¶", "&#182;", "¶" }, { "·", "&#183;", "·" }, { "¸", "&#184;", "¸" }, { "¹", "&#185;", "¹" }, { "º", "&#186;", "º" }, { "»", "&#187;", "»" }, { "¿", "&#191;", "¿" }, { "‐", "&#8208;", "‐" }, { "‑", "&#8209;", " " }, { "‒", "&#8210;", " " }, { "–", "&#8211;", "–" }, { "—", "&#8212;", "—" }, { "―", "&#8213;", "―" }, { "‖", "&#8214;", "‖" }, { "‗", "&#8215;", " " }, { "‘", "&#8216;", "‘" }, { "’", "&#8217;", "’" }, { "‚", "&#8218;", "‚" }, { "‛", "&#8219;", " " }, { "“", "&#8220;", "“" }, { "”", "&#8221;", "”" }, { "„", "&#8222;", "„" }, { "‟", "&#8223;", " " }, { "†", "&#8224;", "†" }, { "‡", "&#8225;", "‡" }, { "•", "&#8226;", "•" }, { "‣", "&#8227;", " " }, { "․", "&#8228;", " " }, { "‥", "&#8229;", "‥" }, { "…", "&#8230;", "…" }, { "‧", "&#8231;", " " }, { "‰", "&#8240;", "‰" }, { "‱", "&#8241;", "‱" }, { "′", "&#8242;", "′" }, { "″", "&#8243;", "″" }, { "‴", "&#8244;", "‴" }, { "‵", "&#8245;", "‵" }, { "‶", "&#8246;", " " }, { "‷", "&#8247;", " " }, { "‸", "&#8248;", " " }, { "‹", "&#8249;", "‹" }, { "›", "&#8250;", "›" }, { "※", "&#8251;", " " }, { "‼", "&#8252;", " " }, { "‽", "&#8253;", " " }, { "‾", "&#8254;", "‾" }, { "‿", "&#8255;", " " }, { "⁀", "&#8256;", " " }, { "⁁", "&#8257;", "⁁" }, { "⁂", "&#8258;", " " }, { "⁃", "&#8259;", "⁃" }, { "⁄", "&#8260;", "⁄" }, { "⁅", "&#8261;", " " }, { "⁆", "&#8262;", " " }, { "⁇", "&#8263;", " " }, { "⁈", "&#8264;", " " }, { "⁉", "&#8265;", " " }, { "⁊", "&#8266;", " " }, { "⁋", "&#8267;", " " }, { "⁌", "&#8268;", " " }, { "⁍", "&#8269;", " " }, { "⁎", "&#8270;", " " }, { "⁏", "&#8271;", "⁏" }, { "⁐", "&#8272;", " " }, { "⁑", "&#8273;", " " }, { "⁒", "&#8274;", " " }, { "⁓", "&#8275;", " " }, { "⁔", "&#8276;", " " }, { "⁕", "&#8277;", " " }, { "⁖", "&#8278;", " " }, { "⁗", "&#8279;", "⁗" }, { "⁘", "&#8280;", " " }, { "⁙", "&#8281;", " " }, { "⁚", "&#8282;", " " }, { "⁛", "&#8283;", " " }, { "⁜", "&#8284;", " " }, { "⁝", "&#8285;", " " }, { "⁞", "&#8286;", " " }, { "™", "&#8482;", "™" } 
    } 
),
decodeHtmlCode = (rawHtmlCode as text) as text =>
    let
        dictionary = List.Buffer(
            List.Select(
                List.Combine(
                    {
                        dictionaryHtmlCode[htmlCode],
                        dictionaryHtmlCode[htmlEntity]
                    }
                ),
                each Text.Trim(_) <> ""
            )
        ) as list
    in
        List.Accumulate(
            dictionary as list,
            rawHtmlCode as text,
            (base as text, thisHtmlEncodedSymbol as text) as text =>
                Text.Replace(
                    base,
                    thisHtmlEncodedSymbol,
                    try
                        dictionaryHtmlCode{[htmlCode = thisHtmlEncodedSymbol]}[char]
                    otherwise
                        dictionaryHtmlCode{[htmlEntity = thisHtmlEncodedSymbol]}[char]
                )
        ) as text

Sources

[1] HTML Punctuation Symbols, Entities and Codes — Toptal Designers
I scraped the codes using the following JavaScript:

    
  var entities = []
  document.querySelectorAll('.entity').forEach((e) => {
      symbol = e.children[1].innerText
      if (symbol !== 'SYMBOL') {
          htmlCode = e.children[2].children[4].innerText
          htmlEntity = e.children[2].children[6].innerText
          entity = {
              symbol: symbol,
              htmlCode: htmlCode,
              htmlEntity: htmlEntity
          }
          entities.push(entity)
      }
  })
  console.log(entities)

Decode SharePoint escape characters

SharePoint will turn special characters into escaped versions. This function will decode those characters. For HTTP-escaped characters, change the line "_x00" & thisSpEscapedSymbol & "_" to "%" & thisSpEscapedSymbol

Code


let
    dictionarySpEncoding = #table(
        {"char", "spEscape"},
        {
            {Character.FromNumber(10), "0D"}, {".", "2E"}, {"-", "2D"}, {" ", "20"}, {"_", "2d"}, {"-", "27"}, {"#", "23"}, {"%", "25"}, {"{", "7B"}, {"}", "7D"}, {"|", "7C"}, {"\", "5C"}, {"^", "5E"}, {"~", "7E"}, {"[", "5B"}, {"]", "5D"}, {"`", "60"}, {";", "3B"}, {"/", "2F"}, {"?", "3F"}, {":", "3A"}, {"@", "40"}, {"=", "3D"}, {"&", "26"}, {"$", "24"}
        }
    ),
    decodeEscape = (rawText as text) as text =>
        let 
            dictionary = List.Buffer(dictionarySpEncoding[spEscape])
        in
            List.Accumulate(
                dictionary as list,
                rawText as text,
                (base as text, thisSpEscapedSymbol as text) as text =>
                    Text.Replace(
                        base,
                        "_x00" & thisSpEscapedSymbol & "_",
                        dictionarySpEncoding{[spEscape = thisSpEscapedSymbol]}[char]
                    )
            ) as text
in
    decodeEscape

Skip N Words

Code


(phrase as text, n as number) as text =>
    let
        delimiter = " "
    in
        Text.Combine(
            List.Skip(
                Text.Split(phrase, delimiter), 
                n
            ),
            delimiter
        )

Split into array, based on type of character

This function will split a string into a list. Each element in the list is a "bin", where each "bin" is a type of character.

e.g. "firstname.lastname1997@hotmail.com" will be split into {"firstname", ".", "lastname", "1997", "@", "hotmail", ".", "com"}.

ASCII codes


[
    { "Bin.": "0000000", "Hex.": "0", "Dec.": "0", "ASCII Symbol": "NUL", "Explanation": "The null character prompts the device to do nothing", "Group": "Control Character" },
    { "Bin.": "0000001", "Hex.": "1", "Dec.": "1", "ASCII Symbol": "SOH", "Explanation": "Initiates a header (Start of Heading)", "Group": "Control Character" },
    { "Bin.": "0000010", "Hex.": "2", "Dec.": "2", "ASCII Symbol": "STX", "Explanation": "Ends the header and marks the beginning of a message. (start of text)", "Group": "Control Character" },
    { "Bin.": "0000011", "Hex.": "3", "Dec.": "3", "ASCII Symbol": "ETX", "Explanation": "Indicates the end of the message (end of text)", "Group": "Control Character" },
    { "Bin.": "0000100", "Hex.": "4", "Dec.": "4", "ASCII Symbol": "EOT", "Explanation": "Marks the end of a completes transmission (End of Transmission)", "Group": "Control Character" },
    { "Bin.": "0000101", "Hex.": "5", "Dec.": "5", "ASCII Symbol": "ENQ", "Explanation": "A request that requires a response (Enquiry)", "Group": "Control Character" },
    { "Bin.": "0000110", "Hex.": "6", "Dec.": "6", "ASCII Symbol": "ACK", "Explanation": "Gives a positive answer to the request (Acknowledge)", "Group": "Control Character" },
    { "Bin.": "0000111", "Hex.": "7", "Dec.": "7", "ASCII Symbol": "BEL", "Explanation": "Triggers a beep (Bell)", "Group": "Control Character" },
    { "Bin.": "0001000", "Hex.": "8", "Dec.": "8", "ASCII Symbol": "BS", "Explanation": "Lets the cursor move back one step (Backspace)", "Group": "Control Character" },
    { "Bin.": "0001001", "Hex.": "9", "Dec.": "9", "ASCII Symbol": "TAB (HT)", "Explanation": "A horizontal tab that moves the cursor within a row to the next predefined position (Horizontal Tab)", "Group": "Control Character" },
    { "Bin.": "0001010", "Hex.": "A", "Dec.": "10", "ASCII Symbol": "LF", "Explanation": "Causes the cursor to jump to the next line (Line Feed)", "Group": "Control Character" },
    { "Bin.": "0001011", "Hex.": "B", "Dec.": "11", "ASCII Symbol": "VT", "Explanation": "The vertical tab lets the cursor jump to a predefined line (Vertical Tab)", "Group": "Control Character" },
    { "Bin.": "0001100", "Hex.": "C", "Dec.": "12", "ASCII Symbol": "FF", "Explanation": "Requests a page break (Form Feed)", "Group": "Control Character" },
    { "Bin.": "0001101", "Hex.": "D", "Dec.": "13", "ASCII Symbol": "CR", "Explanation": "Moves the cursor back to the first position of the line (Carriage Return)", "Group": "Control Character" },
    { "Bin.": "0001110", "Hex.": "E", "Dec.": "14", "ASCII Symbol": "SO", "Explanation": "Switches to a special presentation (Shift Out)", "Group": "Control Character" },
    { "Bin.": "0001111", "Hex.": "F", "Dec.": "15", "ASCII Symbol": "SI", "Explanation": "Switches the display back to the normal state (Shift In)", "Group": "Control Character" },
    { "Bin.": "0010000", "Hex.": "10", "Dec.": "16", "ASCII Symbol": "DLE", "Explanation": "Changes the meaning of the following characters (Data Link Escape)", "Group": "Control Character" },
    { "Bin.": "0010001", "Hex.": "11", "Dec.": "17", "ASCII Symbol": "DC1", "Explanation": "Control characters assigned depending on the device used (Device Control)", "Group": "Control Character" },
    { "Bin.": "0010010", "Hex.": "12", "Dec.": "18", "ASCII Symbol": "DC2", "Explanation": "Control characters assigned depending on the device used (Device Control)", "Group": "Control Character" },
    { "Bin.": "0010011", "Hex.": "13", "Dec.": "19", "ASCII Symbol": "DC3", "Explanation": "Control characters assigned depending on the device used (Device Control)", "Group": "Control Character" },
    { "Bin.": "0010100", "Hex.": "14", "Dec.": "20", "ASCII Symbol": "DC4", "Explanation": "Control characters assigned depending on the device used (Device Control)", "Group": "Control Character" },
    { "Bin.": "0010101", "Hex.": "15", "Dec.": "21", "ASCII Symbol": "NAK", "Explanation": "Negative response to a request (Negative Acknowledge)", "Group": "Control Character" },
    { "Bin.": "0010110", "Hex.": "16", "Dec.": "22", "ASCII Symbol": "SYN", "Explanation": "Synchronizes a data transfer, even if no signals are transmitted (Synchronous Idle)", "Group": "Control Character" },
    { "Bin.": "0010111", "Hex.": "17", "Dec.": "23", "ASCII Symbol": "ETB", "Explanation": "Marks the end of a transmission block (End of Transmission Block)", "Group": "Control Character" },
    { "Bin.": "0011000", "Hex.": "18", "Dec.": "24", "ASCII Symbol": "CAN", "Explanation": "Makes it clear that a transmission was faulty and the data must be discarded (Cancel)", "Group": "Control Character" },
    { "Bin.": "0011001", "Hex.": "19", "Dec.": "25", "ASCII Symbol": "EM", "Explanation": "Indicates the end of the storage medium (End of Medium)", "Group": "Control Character" },
    { "Bin.": "0011010", "Hex.": "1A", "Dec.": "26", "ASCII Symbol": "SUB", "Explanation": "Replacement for a faulty sign (Substitute)", "Group": "Control Character" },
    { "Bin.": "0011011", "Hex.": "1B", "Dec.": "27", "ASCII Symbol": "ESC", "Explanation": "Initiates an escape sequence and thus gives the following characters a special meaning (Escape)", "Group": "Control Character" },
    { "Bin.": "0011100", "Hex.": "1C", "Dec.": "28", "ASCII Symbol": "FS", "Explanation": "Marks the separation of logical data blocks and is hierarchically ordered: file as the largest unit, file as the smallest unit.(File Separator, Group Separator, Record Separator, Unit Separator)", "Group": "Control Character" },
    { "Bin.": "0011101", "Hex.": "1D", "Dec.": "29", "ASCII Symbol": "GS", "Explanation": "Marks the separation of logical data blocks and is hierarchically ordered: file as the largest unit, file as the smallest unit.(File Separator, Group Separator, Record Separator, Unit Separator)", "Group": "Control Character" },
    { "Bin.": "0011110", "Hex.": "1E", "Dec.": "30", "ASCII Symbol": "RS", "Explanation": "Marks the separation of logical data blocks and is hierarchically ordered: file as the largest unit, file as the smallest unit.(File Separator, Group Separator, Record Separator, Unit Separator)", "Group": "Control Character" },
    { "Bin.": "0011111", "Hex.": "1F", "Dec.": "31", "ASCII Symbol": "US", "Explanation": "Marks the separation of logical data blocks and is hierarchically ordered: file as the largest unit, file as the smallest unit.(File Separator, Group Separator, Record Separator, Unit Separator)", "Group": "Control Character" },
    { "Bin.": "0100000", "Hex.": "20", "Dec.": "32", "ASCII Symbol": "SP", "Explanation": "Blank space (Space)", "Group": "Special Character" },
    { "Bin.": "0100001", "Hex.": "21", "Dec.": "33", "ASCII Symbol": "!", "Explanation": "Exclamation mark", "Group": "Special Character" },
    { "Bin.": "0100010", "Hex.": "22", "Dec.": "34", "ASCII Symbol": " ", "Explanation": "Only quotes above", "Group": "Special Character" },
    { "Bin.": "0100011", "Hex.": "23", "Dec.": "35", "ASCII Symbol": "#", "Explanation": "Pound sign", "Group": "Special Character" },
    { "Bin.": "0100100", "Hex.": "24", "Dec.": "36", "ASCII Symbol": "$", "Explanation": "Dollar sign", "Group": "Special Character" },
    { "Bin.": "0100101", "Hex.": "25", "Dec.": "37", "ASCII Symbol": "%", "Explanation": "Percentage sign", "Group": "Special Character" },
    { "Bin.": "0100110", "Hex.": "26", "Dec.": "38", "ASCII Symbol": "&", "Explanation": "Commericial and", "Group": "Special Character" },
    { "Bin.": "0100111", "Hex.": "27", "Dec.": "39", "ASCII Symbol": " ", "Explanation": "Apostrophe", "Group": "Special Character" },
    { "Bin.": "0101000", "Hex.": "28", "Dec.": "40", "ASCII Symbol": "(", "Explanation": "Left bracket", "Group": "Special Character" },
    { "Bin.": "0101001", "Hex.": "29", "Dec.": "41", "ASCII Symbol": ")", "Explanation": "Right bracket", "Group": "Special Character" },
    { "Bin.": "0101010", "Hex.": "2A", "Dec.": "42", "ASCII Symbol": "*", "Explanation": "Asterisk", "Group": "Special Character" },
    { "Bin.": "0101011", "Hex.": "2B", "Dec.": "43", "ASCII Symbol": "+", "Explanation": "Plus symbol", "Group": "Special Character" },
    { "Bin.": "0101100", "Hex.": "2C", "Dec.": "44", "ASCII Symbol": ",", "Explanation": "Comma", "Group": "Special Character" },
    { "Bin.": "0101101", "Hex.": "2D", "Dec.": "45", "ASCII Symbol": "-", "Explanation": "Dash", "Group": "Special Character" },
    { "Bin.": "0101110", "Hex.": "2E", "Dec.": "46", "ASCII Symbol": ".", "Explanation": "Full stop", "Group": "Special Character" },
    { "Bin.": "0101111", "Hex.": "2F", "Dec.": "47", "ASCII Symbol": "/", "Explanation": "Forward slash", "Group": "Special Character" },
    { "Bin.": "0110000", "Hex.": "30", "Dec.": "48", "ASCII Symbol": "0", "Explanation": " ", "Group": "Numbers" },
    { "Bin.": "0110001", "Hex.": "31", "Dec.": "49", "ASCII Symbol": "1", "Explanation": " ", "Group": "Numbers" },
    { "Bin.": "0110010", "Hex.": "32", "Dec.": "50", "ASCII Symbol": "2", "Explanation": " ", "Group": "Numbers" },
    { "Bin.": "0110011", "Hex.": "33", "Dec.": "51", "ASCII Symbol": "3", "Explanation": " ", "Group": "Numbers" },
    { "Bin.": "0110100", "Hex.": "34", "Dec.": "52", "ASCII Symbol": "4", "Explanation": " ", "Group": "Numbers" },
    { "Bin.": "0110101", "Hex.": "35", "Dec.": "53", "ASCII Symbol": "5", "Explanation": " ", "Group": "Numbers" },
    { "Bin.": "0110110", "Hex.": "36", "Dec.": "54", "ASCII Symbol": "6", "Explanation": " ", "Group": "Numbers" },
    { "Bin.": "0110111", "Hex.": "37", "Dec.": "55", "ASCII Symbol": "7", "Explanation": " ", "Group": "Numbers" },
    { "Bin.": "0111000", "Hex.": "38", "Dec.": "56", "ASCII Symbol": "8", "Explanation": " ", "Group": "Numbers" },
    { "Bin.": "0111001", "Hex.": "39", "Dec.": "57", "ASCII Symbol": "9", "Explanation": " ", "Group": "Numbers" },
    { "Bin.": "0111010", "Hex.": "3A", "Dec.": "58", "ASCII Symbol": ":", "Explanation": "Colon", "Group": "Special characters" },
    { "Bin.": "0111011", "Hex.": "3B", "Dec.": "59", "ASCII Symbol": ";", "Explanation": "Semicolon", "Group": "Special characters" },
    { "Bin.": "0111100", "Hex.": "3C", "Dec.": "60", "ASCII Symbol": "<", "Explanation": "Small than bracket", "Group": "Special characters" },
    { "Bin.": "0111101", "Hex.": "3D", "Dec.": "61", "ASCII Symbol": "=", "Explanation": "Equals sign", "Group": "Special characters" },
    { "Bin.": "0111110", "Hex.": "3E", "Dec.": "62", "ASCII Symbol": ">", "Explanation": "Bigger than symbol", "Group": "Special characters" },
    { "Bin.": "0111111", "Hex.": "3F", "Dec.": "63", "ASCII Symbol": "?", "Explanation": "Question mark", "Group": "Special characters" },
    { "Bin.": "1000000", "Hex.": "40", "Dec.": "64", "ASCII Symbol": "@", "Explanation": "At symbol", "Group": "Special characters" },
    { "Bin.": "1000001", "Hex.": "41", "Dec.": "65", "ASCII Symbol": "A", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1000010", "Hex.": "42", "Dec.": "66", "ASCII Symbol": "B", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1000011", "Hex.": "43", "Dec.": "67", "ASCII Symbol": "C", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1000100", "Hex.": "44", "Dec.": "68", "ASCII Symbol": "D", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1000101", "Hex.": "45", "Dec.": "69", "ASCII Symbol": "E", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1000110", "Hex.": "46", "Dec.": "70", "ASCII Symbol": "F", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1000111", "Hex.": "47", "Dec.": "71", "ASCII Symbol": "G", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1001000", "Hex.": "48", "Dec.": "72", "ASCII Symbol": "H", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1001001", "Hex.": "49", "Dec.": "73", "ASCII Symbol": "I", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1001010", "Hex.": "4A", "Dec.": "74", "ASCII Symbol": "J", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1001011", "Hex.": "4B", "Dec.": "75", "ASCII Symbol": "K", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1001100", "Hex.": "4C", "Dec.": "76", "ASCII Symbol": "L", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1001101", "Hex.": "4D", "Dec.": "77", "ASCII Symbol": "M", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1001110", "Hex.": "4E", "Dec.": "78", "ASCII Symbol": "N", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1001111", "Hex.": "4F", "Dec.": "79", "ASCII Symbol": "O", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1010000", "Hex.": "50", "Dec.": "80", "ASCII Symbol": "P", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1010001", "Hex.": "51", "Dec.": "81", "ASCII Symbol": "Q", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1010010", "Hex.": "52", "Dec.": "82", "ASCII Symbol": "R", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1010011", "Hex.": "53", "Dec.": "83", "ASCII Symbol": "S", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1010100", "Hex.": "54", "Dec.": "84", "ASCII Symbol": "T", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1010101", "Hex.": "55", "Dec.": "85", "ASCII Symbol": "U", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1010110", "Hex.": "56", "Dec.": "86", "ASCII Symbol": "V", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1010111", "Hex.": "57", "Dec.": "87", "ASCII Symbol": "W", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1011000", "Hex.": "58", "Dec.": "88", "ASCII Symbol": "X", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1011001", "Hex.": "59", "Dec.": "89", "ASCII Symbol": "Y", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1011010", "Hex.": "5A", "Dec.": "90", "ASCII Symbol": "Z", "Explanation": " ", "Group": "Capital letters" },
    { "Bin.": "1011011", "Hex.": "5B", "Dec.": "91", "ASCII Symbol": "[", "Explanation": "Left square bracket", "Group": "Special character" },
    { "Bin.": "1011100", "Hex.": "5C", "Dec.": "92", "ASCII Symbol": "\\", "Explanation": "Inverse/backward slash", "Group": "Special character" },
    { "Bin.": "1011101", "Hex.": "5D", "Dec.": "93", "ASCII Symbol": "]", "Explanation": "Right square bracket", "Group": "Special character" },
    { "Bin.": "1011110", "Hex.": "5E", "Dec.": "94", "ASCII Symbol": "^", "Explanation": "Circumflex", "Group": "Special character" },
    { "Bin.": "1011111", "Hex.": "5F", "Dec.": "95", "ASCII Symbol": "_", "Explanation": "Underscore", "Group": "Special character" },
    { "Bin.": "1100000", "Hex.": "60", "Dec.": "96", "ASCII Symbol": "`", "Explanation": "Gravis (backtick)", "Group": "Special character" },
    { "Bin.": "1100001", "Hex.": "61", "Dec.": "97", "ASCII Symbol": "a", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1100010", "Hex.": "62", "Dec.": "98", "ASCII Symbol": "b", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1100011", "Hex.": "63", "Dec.": "99", "ASCII Symbol": "c", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1100100", "Hex.": "64", "Dec.": "100", "ASCII Symbol": "d", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1100101", "Hex.": "65", "Dec.": "101", "ASCII Symbol": "e", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1100110", "Hex.": "66", "Dec.": "102", "ASCII Symbol": "f", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1100111", "Hex.": "67", "Dec.": "103", "ASCII Symbol": "g", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1101000", "Hex.": "68", "Dec.": "104", "ASCII Symbol": "h", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1101001", "Hex.": "69", "Dec.": "105", "ASCII Symbol": "i", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1101010", "Hex.": "6A", "Dec.": "106", "ASCII Symbol": "j", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1101011", "Hex.": "6B", "Dec.": "107", "ASCII Symbol": "k", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1101100", "Hex.": "6C", "Dec.": "108", "ASCII Symbol": "l", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1101101", "Hex.": "6D", "Dec.": "109", "ASCII Symbol": "m", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1101110", "Hex.": "6E", "Dec.": "110", "ASCII Symbol": "n", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1101111", "Hex.": "6F", "Dec.": "111", "ASCII Symbol": "o", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1110000", "Hex.": "70", "Dec.": "112", "ASCII Symbol": "p", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1110001", "Hex.": "71", "Dec.": "113", "ASCII Symbol": "q", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1110010", "Hex.": "72", "Dec.": "114", "ASCII Symbol": "r", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1110011", "Hex.": "73", "Dec.": "115", "ASCII Symbol": "s", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1110100", "Hex.": "74", "Dec.": "116", "ASCII Symbol": "t", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1110101", "Hex.": "75", "Dec.": "117", "ASCII Symbol": "u", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1110110", "Hex.": "76", "Dec.": "118", "ASCII Symbol": "v", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1110111", "Hex.": "77", "Dec.": "119", "ASCII Symbol": "w", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1111000", "Hex.": "78", "Dec.": "120", "ASCII Symbol": "x", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1111001", "Hex.": "79", "Dec.": "121", "ASCII Symbol": "y", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1111010", "Hex.": "7A", "Dec.": "122", "ASCII Symbol": "z", "Explanation": " ", "Group": "Lowercase letters" },
    { "Bin.": "1111011", "Hex.": "7B", "Dec.": "123", "ASCII Symbol": "{", "Explanation": "Left curly bracket", "Group": "Special characters" },
    { "Bin.": "1111100", "Hex.": "7C", "Dec.": "124", "ASCII Symbol": "l", "Explanation": "Vertical line", "Group": "Special characters" },
    { "Bin.": "1111101", "Hex.": "7D", "Dec.": "125", "ASCII Symbol": "}", "Explanation": "Right curly brackets", "Group": "Special characters" },
    { "Bin.": "1111110", "Hex.": "7E", "Dec.": "126", "ASCII Symbol": "~", "Explanation": "Tilde", "Group": "Special characters" },
    { "Bin.": "1111111", "Hex.": "7F", "Dec.": "127", "ASCII Symbol": "DEL", "Explanation": "Deletes a character. Since this control character consists of the same number on all positions, during the typewriter era it was possible to invalidate another character by punching out all the positions (Delete)", "Group": "Control characters" }
]

Code


let
    map = (list as list, function as function) as list =>
        List.Transform(list, function),
    characterArray = Text.ToList(source),
    #"Converted to Table" = Table.FromList(characterArray, Splitter.SplitByNothing(), {"Character"}, null, ExtraValues.Error),
    addCharacterType = Table.AddColumn(
        #"Converted to Table", 
        "Group", 
        each 
            let
                thisCharacter = [Character]
            in
                Table.SelectRows(
                    asciiCodes,
                    each [#"ASCII Symbol"] = thisCharacter
                ){0}[Group]
    ),
    addIndex = Table.Buffer(
        Table.AddIndexColumn(addCharacterType, "Index", 0, 1, Int64.Type)
    ),
    characterTable = Table.AddColumn(
        addIndex, 
        "SimilarToNext",
        each 
            if [Index] <> List.Max(addIndex[Index]) 
            then addIndex{[Index]}[Group] = addIndex{[Index] + 1}[Group] 
            else false
    ),
    boundaries = Table.SelectRows(characterTable, each ([SimilarToNext] = false)),
    numberOfBins = Table.RowCount(boundaries),
    upperBounds = boundaries[Index],
    lowerBounds = List.Combine(
        {
            {0}, 
            List.Transform(List.RemoveLastN(upperBounds, 1), each _ + 1)
        }
    ),
    bins = List.Transform(
        List.Numbers(0, numberOfBins),
        each [
            lower = lowerBounds{_},
            upper = upperBounds{_}
        ]
    ),
    wordArray = List.Transform(
        bins, 
        each 
            let
                lower = [lower],
                upper = [upper]
            in
                Table.SelectRows(
                    characterTable, 
                    each lower <= [Index] and [Index] <= upper
                )
    ),
    final = List.Transform(
        wordArray, 
        each Text.Combine([Character])
    )
in
final

Sources

[1] Remove HTML Tags in Power Query | Power BI Exchange
[2] Robust function to remove HTML tags - Microsoft Power BI Community
There are probably more...

Strip HTML tags

Sometimes, SharePoint lists contain HTML text data in them, and I'd like to view just the innerText without the tags. I found this on several sources. I've modified the intermediate step names to be more descriptive, and also kept the output as a list rather than a combined text. It might be more versatile this way, I hope you think so too.

Note the incredibly elegant use of List.Alternate() to 'skip' over the positions where the tag names used to be.

Code


(htmlText as text) as list => 
    let
        rawText = Text.From(htmlText),
        splitByAngleBrackets = Text.SplitAny(Source,"<>"),
        skipOverTagNames = List.Alternate(splitByAngleBrackets,1,1,1),
        keepInnerText = List.Select(skipOverTagNames, each Text.Trim(_) <> "") as list
    in
        keepInnerText

Sources

[1] Remove HTML Tags in Power Query | Power BI Exchange
[2] Robust function to remove HTML tags - Microsoft Power BI Community
There are probably more...

Convert table to HTML

Power Query as a templating engine? Sign me up.

This will turn a table into a single HTML text value. Some ideas on what to do once you get it:

Pipe it into a HTML viewer in Power BI (like this one for example)
If you Power Query in Excel, you can pull in the text into Power Apps and render it in HTML straight away. This might save on front-end processing effort compared to composing the HTML in Power Apps code and trying to render it dynamically all at the same time.
???

But why?

The big disadvantage of course is a HTML table won't have any resizing or sorting capabilities like a "native" table in Power BI would. However, if what you need is a static table that is styled exactly the way you like using CSS, then this could be one way to go.

Code


(source as table) as text =>
        let
            // linebreak and tab-indent characters
            nl = Character.FromNumber(10),
            t = Character.FromNumber(9),
            // make the header row
            convertListToHtml = (htmlTag as text, source as list) as text => 
                Text.Combine(
                    List.Transform(
                        source,
                        each html(htmlTag, _)
                    )
                ),
            headerTh = convertListToHtml("th", Table.ColumnNames(source)),
            headerTr = html("tr", headerTh),
            // make the body rows
            bodyTr = Table.TransformRows(
                source,
                each html(
                    "tr", 
                    Text.Combine(
                        Record.FieldValues(
                            Record.TransformFields(
                                _,
                                List.Transform(
                                    Record.FieldNames(_),
                                    each {
                                        _,
                                        each                                           
                                            html(
                                                "td", 
                                                Text.Replace(
                                                    Text.From(_),
                                                    Character.FromNumber(10),
                                                    "<br>"
                                                )
                                            )
                                    }
                                )
                            )
                        )
                    )
                )
            ),
            body = Text.Combine(bodyTr),
            htmlTable = html("table", headerTr & body)
        in
            htmlTable

Convert list to HTML

This will turn a list type into a single HTML text value, that is a list.

The second argument accepts either "ol" or "ul", and will render an ordered (numbered) or an unordered (bulleted) list respectively. If you feed it any other string, it will default to "ul".

Code


(source as list, tagType as text) as text => 
    let 
        tag = if tagType = "ol" then "ol" else "ul",
        openTag = "<" & tag & ">",
        closeTag = "</" & tag & ">",
        // tab character
        t = Character.FromNumber(9),
        // newline character
        nl = Character.FromNumber(10),
        putTags = List.Transform(
            source, 
            each t & "<li>"
                & Text.Replace(
                    Text.From(_),
                    Character.FromNumber(10),
                    "<br>"
                )
                & "</li>" 
                & nl
        ),
        htmlList = openTag & nl & Text.Combine(putTags) & closeTag
    in
        htmlList

Create HTML tag

Code


(tag as text, innerHtml as text, optional attributes as nullable record) => 
    let
        attributeRecordToText = (optional attributes as nullable record) as text => 
            let
                convertToTable = Record.ToTable(attributes),
                listOfAttributes = Table.TransformRows(
                    convertToTable,
                    each [Name] & "=""" & [Value] & """"
                ),
                combinedAttributesText = " " & Text.Combine(listOfAttributes, " ")
            in 
                try combinedAttributesText otherwise "",
        attributes = default(attributeRecordToText(attributes), "") as text,
        openTag = "<" & tag & attributes & ">",
        closeTag = "</" & tag & ">"
    in
        openTag & innerHtml & closeTag

Regex find and replace

This function will find text according to a regex pattern and replace it.

Code


(inputText as text, searchRegex as text, replacementRegex as text) as text =>
    let
        Source = Web.Page(
            "
                <script>
                    var x = " & "'" & inputText & "'" & ";
                    var y = new RegExp('" & searchRegex & "','g');
                    var z = " & "'" & replacementRegex & "'" & ";
                    var b = x.replace(y, z);
                    document.write(b);
                </script>
            "
        )[Data]{0}[Children]{0}[Children]{1}[Text]{0}
    in
        Source

Source

[1] Power Query Regular Expression Hack | Math Encounters Blog

Date and time functions

Now

Code


now = () as time =>
    Time.From(DateTime.LocalNow())

Today

Code


today = () as date =>
        Date.From(DateTime.LocalNow())

SharePoint function

Get site contents

Code


(sharepointSiteRoot as text) as table => 
    // e.g. https://DOMAIN.sharepoint.com/sites/SITENAME/
    OData.Feed(sharepointSiteRoot & "/_vti_bin/listdata.svc", null, [Implementation="2.0"])

Power Query functions

Contents

Utility functions

Default function parameter

Switch

Custom sorting function

Table functions

Adding multiple table columns

Add column to front

Add total row

Add an ID column on the far left

Base Name for File

Count If

Describe table

Expand record, all fields

Multiple merge columns

Profile (including uniqueness)

Remove default columns

Remove empty columns

Replace multiple values

Select columns "up to"

Thin out table

Transform column if

List functions

Generate character list

Sequence

Shuffle

List Remove Errors

Text functions

Contains All

Decode URL ("percent-encoded")

Decode HTML character codes ("ampersand encoded")

Decode SharePoint escape characters

Skip N Words

Split into array, based on type of character

Strip HTML tags

Convert table to HTML

Convert list to HTML

Create HTML tag

Regex find and replace

Date and time functions

Now

Today

SharePoint function

Get site contents