Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
I have been working on a project where we want to combine the Input from PowerApps with Power BI's reporting.
The problem I am currently encountering is using Excel workbooks of type .xlsx that were uploaded using PowerApps as a datasource for Power BI.
Since these files are uploaded into SharePoint, I thought of pointing to the list containing these uploads and it returned to site URL for downloading the file.
Is there some way to use this URL - or any alternative - to connect to the uploaded worksheets using Power Query?
Solved! Go to Solution.
Hello @Anonymous
I think it should work like this
Excel.Workbook(Web.Contents(YOURPATHFROMPOWERAPP))
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Anonymous
I think it should work like this
Excel.Workbook(Web.Contents(YOURPATHFROMPOWERAPP))
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Thanks @Jimmy801 this seems to work for pointing to a single file.
Do you have any suggestions on how I can append the data of multiple files like they are stored in my example?
I tried playing around with the SharePoint.Contents and SharePoint.Files data access functions, but with no success.
Hello @Anonymous
I'm not an expert, but I think there is a difference in Sharepoint and Sharepoint online. And It could also be that your administrator somehow blocked the access of streaming services like Power BI. How I said, I'm not an expert.
I've tried with this function, and it works
= SharePoint.Contents("YOUSHAREPOINT", [ApiVersion = 15])
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
I went and tried using parameters with what you suggested. So far it seems like progress in the right direction.
I created a funtion that I can use in conjunction with a table containing the second portion of the URL (like in the original example) where each file is uploaded
(parPath as any) =>
let
Source = Excel.Workbook(Web.Contents("https://company.sharepoint.com" & parPath))
in
Source
I then created a new query which is supposed to call the funtion and return a table of tables containing the data of each file. Following this guide.
let
Source = Paths,
path_name = Table.ToList(Table.SelectColumns(Source, "ShortPath")),
file_path = List.Transform(path_name, each get_path(_))
in
file_path
My only problem now is I am getting an error I do not quite understand and suspect that you might be right about the blocked access.
Hello @Anonymous
include the function in your query or deactivate the firewall of power BI
BR
Jimmy
Hi @Jimmy801
Good news, including the funtion in my query did the trick. I also replace Source = Paths with the full referenced query.
Thanks for the help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.