PowerApps attachments as datasource

[Anonymous]

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?

[Jimmy801 · Accepted 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

[Anonymous]

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.

[Jimmy801]

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

[Anonymous]

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.

[Jimmy801]

Hello @Anonymous 

include the function in your query or deactivate the firewall of power BI

BR

Jimmy

[Anonymous]

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.