Alteryx, Power BI, or Excel Issue

[christopherwade1]

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

CW

[christopherwade1]

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

CW

[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.

M.

[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!