Alteryx, Power BI, or Excel Issue

Good day all.

Got an odd issue, I'm trying to figure out if it is an Alteryx or Power BI issue.

My PBI reads data from an Excel file that is fed by an Alteryx Workflow. The workflow simply pulls two Excel worksheets together, cleans them and outputs the results to a third Excel workbook. It appends the file each time it runs.

On the PBI side, the report reads the data and does it's thing.

When I manually update the source .xlsx, PBI reads the appended data just fine. However, when I append the data using Alteryx, PBI does not read the new data at all.

OK, now here is the weird thing, I open the file and the new Alteryx data is there. Close and it still won't read it. But if I save the file manually, PBI picks up the new data as one would expect.

I have completely recreated the Excel file from scratch. Emptied it except the headers and run the Alteryx, PBI does not see a thing until I save the Excel manually. I have made sure there are no ghost rows or columns by manually deleting all the empty ones. I am befuddled.

Does anyone have any ideas? Can share the files if you want.

Thanks

Output

Accepted answers

All comments

ImadZidan

Hello @christopherwade1 ,

I have a feeling it is a caching issue. Is PBI placing the source file and reading it from a different location ?

Just an idea.

christopherwade1

Ah, good thought. I have not published the report yet so it is still local. I did however, check the local cache and cleared it just in case but no change. I tried it limited to my computer and limited to the company network drive, no change.

Thanks.

ChrisWright

I have this exact issue also. Did you find any solution ?

davidskaife

Hi @christopherwade1

I've encountered this issue with a client before, try the below in Power BI:

In the query editor/Advanced Editor;
The first row of code will look something similar to Source = Excel.Workbook(File.Contents("C:\Users\David.Skaife\Desktop\Test.xlsm"), null, true),
Replace null,true with the following code [UseHeaders = false, DelayTypes = true, InferSheetDimensions = true]
Close and apply the changes, and your data should start appearing when you refresh Power BI

i cannot recall the full reasons behind this but it's something to do with Excel not updating correctly in the background, so Power BI doesn't know there is new data there. The above fix should work as a workaround.

FreeRangeDingo

@christopherwade1 We are running into this problem. We have a Power Automate flow that updates a spreadsheet used in a PBI report. When PBI updates, it doesn't pick up the new data. We tried the solution that you mentioned but it didn't work. Do you have any additional information since this posting?

Aguisande

I saw this with formulas in Excel.

Can you try to use a different file type to test it? Like a CSV/access or even better a database.

FreeRangeDingo

@Aguisande, @christopherwade1, @davidskaife

I think I have solved this problem. I added a Block Until Done tool and a Python tool. Block Until Done makes sure that my Excel file is updated before I reopen it and refresh the formulas with Python.

My python code is this

from ayx import Alteryx
from ayx import Package

#Alteryx.installPackages(package="xlwings")

import xlwings as xw
app = xw.App()
wbPath = r'\\networklocation\shared_drive\folder\File_name.xlsx'
wb = xw.Book(wbPath)
wb.api.RefreshAll()
wb.save(wbPath)
app.quit()

Thanks to @MarqueeCrew and the other ACES that helped me with this today.

Aguisande

Hi @FreeRangeDingo

I'll definitely try this! Is way a better method that all the ones we tried to apply (read as zip/XML, Excel macro to refresh, etc).

Thanks a lot!

davidskaife

Hi @FreeRangeDingo

Thanks for this, good to know there is a way of fixing it on the Alteryx end without having to tinker with Power BI

mceleavey

@christopherwade1 if you're using the cloud version of Power BI then you can connect Alteryx directly to the back-end datasets, and therefore update automatically, removing the need for Excel and removing the intermediate steps as well.

@TheOC wrote a manual on how to set up your Azure instance allow Alteryx to connect and manage the datasets.

DM one of us if you'd like to know more about that.

FreeRangeDingo

When you say "let Alteryx manage the data sets", what does that mean? I don't think I needed Alteryx to manage the data sets unless I just don't understand what that means.

FreeRangeDingo

I had this exact problem. I tried to solve it by writing a Python script to open the Excel file, update the file (update formulas), save, and close. It worked on Desktop, but not on gallery because Excel requires an active user session. You have an active user session when running on desktop but not in the Gallery. The only way around this is to do all of your calculations in Alteryx and write the entire result to a worksheet that PBI picks up....OR....use windows scheduler to run a script....OR use Power Automate desktop to open, update, save, and close the file. It's not an Alteryx problem. It's a problem with how PBI/Excel is built. Just spent weeks on this.

FreeRangeDingo

@davidskaife, @Aguisande UPDATE: The Python script will work on desktop, but it will not work when run from the Gallery. This isn't specific to the script or the package used. I had to reach out to Alteryx support on this. They said, ...The package is opening the Excel File as an interactive user, it will require an active user session to open it, which is fine when run from desktop. For security, Alteryx Gallery only runs as a non-interactive session that does not allow for interactive processes. ....which is really interesting because the error message led you in a completely different direction. So, running a python script from the Gallery to update Excel will never work (unless they change how server works).

So, other potential solutions....

1. Open and close Excel with Power Automate Desktop

2. Run the script thru Windows Scheduler

3. Reconfigure the whole thing and do all the calculations in Alteryx. Then, write an output to Excel where calculations don't have to be refreshed.

priya_mohana_dhl

It worked for me. Thank you.

DevinBurnett

wow! thank you this worked for me!

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157