Automation with Excel, PowerBI, Power Automate & Alteryx! What Could Go Wrong?

One of our users was manually entering data into a spreadsheet every single day rain or shine, weekday or weekened, vacation or not. She needed an automation solution, and I built one for her. But, we ran into a small but time-consuming problem to solve. The automation added data to an Excel spreadsheet, but the updated data didn’t appear in PowerBI. Half the battle was figuring out whether Excel, PowerBI, Power Automate, or Alteryx was causing the problem. Wanna guess which one it was? Read on to find out.

The Data

Okay, the user in question receives 3 pieces of data that needed to be entered into a spreadsheet. One data point arrives via an email Excel attachment once a day. The other two pieces of information she gets via phone or chat or email or hallway conversations at varying time intervals. It’s also worth mentioning that she has to manually change some of this data when estimates become actuals.

The Automation

Now, the user already had a spreadsheet connected to a PowerBI project that a lot of people looked at each day. I didn’t want to mess with that. I wanted to keep the solution as simple as possible. Therefore, I structured the automation around moving data from email to Excel worksheets in her existing workbook. Then, calculations look up the data the automation put into the file. Overall, the solution had 6 components as shown below.

• 3 Power Automate Cloud flows
• 2 Power Automate Desktop flows
• 1 Alteryx workflow

Power Automate monitored an inbox for specific emails. It triggered the automation solution. We used Power Automate to call Power Automate Desktop, which is where are able to hook into the Alteryx API and run an Alteryx workflow from the gallery to do some data wrangling because the Excel attachment looks like this….

And of course, I ran into some complexity with the file types. Alteryx and Power Automate don’t like old Excel file types like .xls so I had to do a conversion in Power Automate desktop. But, it worked nicely! So, what was the problem?

The Problem

The automation solution would run. Data went into Excel, but when users opened PowerBI, they did not see the most recently uploaded data. What gives? Was it something that Alteryx was doing? Did the automation fail? Was PowerBI just not updating? Did we need to connect to a named table or something different in PowerBI?

When Alteryx writes to Excel, it just writes the data. It doesn’t update the calculations. But, PowerBI was pulling from the calculations. The missing step was opening Excel, hitting F9 to update calculations, saving, and closing. Okay, so that’s not too hard. Surely, we can easily do that.

The Solution That Failed

I thought this would be an easy fix in Alteryx. I wrote a Python script that opens the Excel file using the xlwings package after the updated data is written. It looked like this at the end of my workflow.

It worked beautifully when run on desktop, but failed when run from the Gallery. The error indicated a memory problem. I made sure I had the right version of Python. I checked that both Excel and the package were installed on the server. None of those things helped. I even tried a different set of code with a different package. No worky.

This error message seemed like a red herring.

Finally, I reached out to Alteryx support. They said the Excel task requires an active user session. When running on desktop, you get an active user session. But, for security, Alteryx Gallery only runs as a non-interactive session that does not allow for interactive processes. Therefore, it didn’t matter what package or code I used. It would not work, and you cannot perform this task from a workflow run from the Alteryx gallery.

Support said the error message was deceptive but logical. Excel opens and produces a security popup to say “Are you sure you want to open this file using xlwings?” Since the session is non-interactive, and there is no user to click on the pop-up, the task fails. The reason the error suggests memory is because the action is timing out. When something in Excel sticks for a while and then fails to proceed, Microsoft assumes the timeout relates to Windows failing to open Excel in a timely manner.

Granted, I didn’t click on a pop up when I ran the code on my desktop but that’s neither here nor there. You can’t run an active user session from the Gallery. The end. Not possible.

The Successful Solution and Some Other Options

So, what other options do we have available?

1. I could have created a Power Automate Desktop flow to open, update, save and close Excel. In hindsight, that’s what I should have done. It would have been the fastest resolution.
2. Alteryx support suggested using windows task scheduler to run the Python code rather than Alteryx, but I didn’t want to add another moving part.
3. Rebuild the calculations in Alteryx. Have Alteryx export a worksheet that doesn’t need to update calculations, and connect PowerBI to this worksheet.

We went with number 3 because the user was able to do number 3 before I could get to number 1. Now, we have a working solution. PowerBI pulls the updated data.

Conclusion

Overall, I feel like this use case is a prime example of how automating menial tasks seems simple. Really, there are many moving parts and potential pitfalls.

If you’re interested in learning more about Power Automate, check out my Learning Power Automate series. If you came for Alteryx, check out the categories and tags on the right-hand side of the screen.