Announcing Tiller’s 2022 Microsoft Excel Builders Challenge

Hey Greg! I love how you are viewing these platforms as toolboxes and putting them to work for you. That is the superpower of working out of a spreadsheet ecosystem.

Some bullet thoughts…

• Wasn’t familiar with ledger-cli. First thought: cool! Second thought: of course something like that exists…
• Power Query 1/2. We are still getting our heads around this tool. It looks amazing… but most of our team are on Macs and it seems to be only partly supported; I believe pre-built queries will execute on a Mac but there is no supported editor to modify them (correct me if I’m wrong…).
• Power Query 2/2. As for using it in the contest generally, it’s fine. I don’t love that the support isn’t fully cross-platform, but it certainly doesn’t present the complicating security and maintenance issues that scripts do. So… go for it.
• I’m not sure what you mean specifically by “analytics”. We have quite a few Google Sheets templates under the “analysis” umbrella. There is room to port and improve those concepts for Excel. But perhaps you mean something else?
• Mobile-friendly solutions that don’t require scripts or 3rd-party tools are great.
• We’re excited to encourage and support Excel builders, so the contest deadline will likely push out. Look for an official announcement in that respect soon-ish.

Hope this helps. Let me know if I didn’t answer your questions.
We are excited by your enthusiasm.
Randy

Hey @yossiea , I was not familiar with Lambda in Excel, cool!

I am so Power Query focused right now, I may be off base here, but have you seen what Custom Power Query Functions can do?

I just used a custom function to create a Natural Key, akine to a slug in my website world/anyone who’s used WordPress. Transaction.slug (may rename to transactionKey) is composed of the Transaction’s Date, Amount, Description. Probably should include account, and whatever else, but either way, it guarantees that when all special characters are stripped out, and those keys are combined together, to form a unique transactionKey (I will rename it, nicer for docs).

Having that naturally formed (all organic?) transactionKey means any transaction, from any source, has a unique identifier, not tied to the CSV it was imported from, or bank downloaded from, or a random GUID, or incremental number. Nope. Unique key generated by the data, to avoid duplication.

I feel like a marketer pushing my links all over these forums, sorry, but if you checkout the main .xlsx (NOT the macro one, need to delete that), you can go to Data → Queries & Connections → Right Click on createSlug then click Edit. Then click on Advanced Editor in the Home Tab of the Top Ribbon. The code for that is at the end of this post.

Rest of this gets gross cody, but does something like I outline below give you the functionality you are hoping to see with Lambda?

You can download and look at all the code in Tiller-Foundation-Template.xlsx (.xlsx ! Not the one ending with xlsm) from GitHub - gsteve3/tiller-challenge-2022: Entry for Tiller’s 2022 MS Excel Challenge; Hopefully better than resume for Tiller Sr. Engineering Job 😱

The code for adding and setting the custom slug (soon to be transactionKey) is within ImportSourceNormalized:

// near the end of ImportSourceNormalized
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "slug", each createSlug([Date], [Amount], [Description]))

Then the code is also used here, to cover-my-butt in case somehow transactions get into the table without a key. That happens in TransactionsAfterImportDistinctSlug (names all change/refine quickly, overly verbose on purpose initially):

// Replace NULL transactionKeys (slugs) with values from `createSlug(...)` function
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"slug", "originalSlug"}}),
    CreateSlugIfNull = Table.AddColumn(
        #"Renamed Columns",
        "slug",
        each if ([originalSlug] = null)
            then createSlug([Date], [Amount], [Description])
        else
            [originalSlug]
    ),
    #"Removed Columns" = Table.RemoveColumns(CreateSlugIfNull,{"originalSlug"}),

Gross, gross code below, but here is the function:

// function CreateSlug...

let
    CreateSlug = (date, amount, description) => Text.Combine(
        Text.Split(
            Text.Combine({
            Date.ToText(
                date,
                [Format="yyyyMMdd"]
            ),
            Text.Replace(
                Text.Replace(Number.ToText(
                    amount,
                    "F2"
                ), "-", "N"),
                ".",
                "-"
            ),
            Text.Lower(
                Text.Middle(
        //            Text.Remove([Description], "Transfer,from,to, "),
                    Text.Replace(
                        Text.Clean(
                            description
                        ),
                    " ", "-"),
                    0,
                    48
                )
            )
        },
        "-")
    , "-"), 
    "-"
)
in CreateSlug