Get AI to automate spreadsheet work (and get your life back)

AI is steadily becoming an integral part of our lives. There’s hardly anything we cannot do with advanced AI systems, from generating synthetic images and natural language descriptions to autonomous vehicle systems and fraud prevention.

Large Language Models, such as OpenAI’s ChatGPT, for instance, have become invaluable tools to boost our productivity, both at work and in our personal lives. It's no longer unusual to see people turning to AI-driven chatbot assistants for help with their daily tasks.

Spreadsheet enthusiasts, financial analysts, accountants, and data wranglers too can revolutionize the way they work with data, capitalizing on AI's prowess. For financial analysts, AI can help unveil deeper insights into market trends, optimizing investment decisions. Accountants could benefit from streamlined audits and anomaly detection, ensuring data accuracy. Data wranglers can harness AI for guidance in handling massive datasets effortlessly, extracting valuable patterns swiftly. Keeping humans in the loop, AI can become the force multiplier, empowering professionals across these fields to unlock efficiency, reduce errors, and amplify their strategic impact.

KNIME Analytics Platform let’s individuals move away from repetitive, manual spreadsheet work, and start building visual workflows with a low-code data platform – all while being supported by an AI assistant, K-AI, who answers questions and even builds workflows for you. Leveraging AI, any spreadsheet user can start making steps towards more sophisticated analytics, while simultaneously significantly reducing the time they spend crunching data.

In this blog post, we’ll introduce K-AI and explain how it can help spreadsheet users make sense of their data in a simple and fast way. We’ll focus on a typical ETL use case to access, wrangle and visualize customer data. All along the process, we’ll rely on K-AI for Q&A or automated workflow building. Let’s get started!

Disclaimer: K-AI may foster long-term addiction 😉.

If you're new to KNIME Analytics Platform, there’s no better starting point than downloading the latest software version – it’s free!

After that, you may want to explore the Getting Started Guide for KNIME Analytics Platform, browse the KNIME Community Hub in search of workflow solutions, or indulge in the extensive range of learning resources available for free on the KNIME website.

From introductory to specialized courses, certifications, books and cheat sheets, to videos, weekly challenges and blog posts, there’s something for every learner and data professional.

In addition to the wealth of free resources, you might find it helpful to receive guidance when it comes to approaching and developing your workflows. This is where AI can make a difference. The newly introduced KNIME AI Assistant (Labs) extension, available as of KNIME Analytics Platform 5.1, becomes an invaluable ally, assisting in critical decision-making and aiding in node selection and connections.

Note. Please note that K-AI is an experimental service and is actively being developed to improve its performance.

We start off by installing the KNIME AI Assistant (Labs) extension. To do so, we simply drag & drop the extension from the KNIME Community Hub into your KNIME Analytics Platform, and follow the steps of the installation prompt. 

Alternatively, we can select “Info page” → “Install Extensions” and type “knime ai” in the search bar. We select the pertinent extension and click “Next” to complete the procedure.

Upon completion of the installation, a new conversation tab will appear on the left of the user interface, right underneath the KNIME Space Explorer tab, which you can see in (1) on the left of the screenshot below.

Next, we log in to the KNIME Community Hub using the username/email and password of our KNIME account, marked as (2) in the screenshot. If needed, an account can be created for free. Before logging in, it is recommended to read the “Disclaimer” to ensure that the terms of service of the AI system are compatible with the data privacy requirements of our use case.

Once logged in, we are greeted by K-AI and have the option to choose between two modes:

• Q&A. Selecting this mode allows us to converse with K-AI and ask KNIME-specific questions. K-AI can return textual responses and/or node recommendations that can be easily dragged & dropped on the canvas.
• Build. This mode prompts the creation of a KNIME workflow on the canvas.

We’re now all set to leverage AI to streamline workflow building.

Suppose you work as a data analyst at an e-commerce company that ships various products all across the US. The dataset, which contains customer transaction information, might look similar to the one below.

From this dataset, you might wish to construct a pivot table for examining sales across various “Order Dates". Based on this analysis, you may then proceed with the creation of some data visualizations.

With the help of KNIME and K-AI, this is easy.

You can start off by asking K-AI straight away to build the workflow you're aiming to construct. Select the “Build” mode and type your prompt. After a few processing seconds, K-AI automatically drags & drops a sequence of suitable nodes and connects them together, yielding results akin to those shown below.

The AI-generated workflow looks nice, but it can be improved. For example, you might want to further transform your data or explore other types of visualizations to look at it from different perspectives.

To do that, you may find the Q&A feature particularly useful. It allows you to enhance your workflow by seeking suggestions or accessing additional resources. This dynamic interaction with K-AI empowers you to learn while developing workflows.

Suppose you'd like to apply some filters to narrow down your customer transaction dataset to rows with specific characteristics or extract data for a defined range of years. Your interaction with K-AI may look as follows:

First, you might want to convert the column 'Order Date' from String to Date&Time to ensure swift wrangling of date and time information: 

• “How can I convert a string column into a date-time format?”

Next, you could seek help to extract a new column containing only the year from the date column:

• “How can I extract a field from a DateTime?”

Furthermore, you might be interested in filtering rows of transactions happened only in specific states (e.g., New York, Ohio, etc.) or time range, so you may ask K-AI:

• “How can I filter a row?”

From there, you could generate another pivot table and display your data over time using the Line Plot node previously suggested by K-AI. Finally, you might also want to ask how to store the manipulated data in a new Excel file for further analysis.

From a blank canvas, you'll find yourself crafting a functional, well-configured workflow, perfectly tailored to your needs. This seamless process showcases from start to end the power and versatility of KNIME and its in-built AI assistance of K-AI. With each step, you're shaping quickly a logical sequence of data operations that awaits only to be configured.

It’s now time to configure the workflow and execute it. If you’re unsure about how to configure a node, don't hesitate asking K-AI for assistance.

Picking K-AI’s “brain” for suggestions on node configurations, you can easily convert the date information into a dedicated format using the String to Date&Time node, and extract the year from the date column with the help of the Extract Date&Time Fields node, ensuring seamless integration into the pivot table.

With these key steps completed, you're now all set to construct your pivot tables for insightful marketing analysis. For example, you could display the aggregated annual sales generated across different states in 2015-2018, or aggregated sales across different shipping modes.

Lastly, the aggregated data can be visualized swiftly using interactive plots suggested by K-AI and arranged in a sleek dashboard.

To wrap up, you started with nothing but an Excel sheet, and through the guidance of K-AI, you've crafted a simple yet effective workflow for your analysis. Tadaa!

What if you encounter a challenge that K-AI can't solve?

Remember that KNIME isn't just a software, it's a whole community of people. There's a wealth of resources at your disposal: abundant learning resources, plenty of example workflows on the KNIME Community Hub, and many insightful YouTube tutorials created by KNIME experts to help you overcome hurdles and mastering KNIME.

Additionally, the KNIME Community Forum provides a place for you to seek guidance, share knowledge, ask questions, and gain insights from other users. Chances are, if you're facing a particular issue, someone has encountered it before and can offer valuable advice on how to navigate through it.

In this blog post, we've introduced what K-AI is and how it can effectively support your work. Whether you're just starting your KNIME journey or are navigating complex tasks and workflows, K-AI stands as a reliable ally to boost your productivity and outcomes.

From suggesting, selecting and placing the right nodes, to helping with question-answering and node configurations, K-AI can help anyone level up their analytics skills. Its intuitive interface and powerful capabilities enable users to overcome data challenges.

Try K-AI yourself. Download KNIME.