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

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

CW

[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