Using Ch 23 of "M is for (Data) Monkey" in Power BI Desktop

[mitchandsuzy]

Good Morning (or Afternoon),


Thanks for taking the time to read this question.


I have used the parameters as shown in Ch23 in "M is for (Data) Monkey" to allow the files to be moved as required in my PQ / PowerPivot workbooks very extensively.


I have imported the excel file into Power BI Desktop (PBI) from a folder on my desktop and also a folder on my OneDrive.


I have been unable to get either version working. The parameter in the query editor resolves to an HTTP address to my OneDrive in both cases - and neither case works.


Is there some way of doing this in PBI?


If I am ever in a position to use this for work the files will need to be found on a network server so the data can be shared and updated.


Any suggestion you may have will be gratefully accepted.


Suzanne

[Ken Puls]

Hey Suzanne,

So yeah, this is a problem in that there is no ability in M code to read the file path to the solution. The Excel method we use is essentially a hack to read from Excel's ability to get around the issue, but there isn't really a way to work around this in Power BI Desktop.

Having said that, you're storing the data in OneDrive... which OneDrive is it? OneDrive personal or OneDrive for business? Are you simply trying to read from a data source that is held in a subfolder or...

Give me a bit more detail on your data source/solution setup (what are you reading from, etc...) We can read from OneDrive for Business and OneDrive (personal), so maybe we can get this working by ALWAYS reading from the web source. (You can still update the local files, and they'll sync into the web.)

[mitchandsuzy]

Ken,

Thank you so much for taking time to answer my question!

OneDrive is my personal drive - my employer does not have OneDrive.

So, for my developing this - much of which is done at home - I would like to use OneDrive but I also need to use a network drive for this to function at work. So effectively it is a data source in a subfolder.

I would love to learn both techniques OneDrive and folders.

We use SAP at work as the data storage. I run 6 odd queries to create the 6 input files - 4 are excel and 2 are csv. I also have a reference file that has all the extra sheets I need like dates, post codes etc.

I have already done lots of PQ work on these files to enable Power Pivot to create all the lovely charts etc etc. So my hope was to pull this PQ into PowerBI desktop and edit from there - run it on my own work desktop (or at home) and then when it is finished send it to my manager for his viewing pleasure :-))

So I need a location to store the files that is accessible to our team (in a corporately approved manner). These are the people would look at the reports.

Hopefully I have answered the questions you needed.
Thanks again
Suzanne

[Ken Puls]

Hi Suzanne,

If I read correctly what you're after, I think you probably want to set up a personal gateway on your work PC to allow yourself to connect to your data from the cloud.

Have a read of this article and see if that helps. It's a different angle, but may help you avoid the whole "store to OneDrive" part. (The issue you're dealing with is pulling from outside the corporate firewall.)

Your other option is to create the query in Excel with Power Query and land the output in an Excel table. You could then update that workbook while you are at work, save it in OneDrive and connect Power BI Desktop to pull from the OneDrive file. The only thing I'd throw out as a caution here is that this is VERY likely to be outside your IT departments data control policy, so you want to check with them that it is on-side.

[mitchandsuzy]

Thanks for that Ken,

Sorry for the delay in responding - both parents ended up in hospital at the same time (turned out not serious) but it challenged my time management skills.

I ended up using virtually all the original code except I "hard-coded" the folder in the folder query.

As you said I need to make sure I adhere to company policy, so this was the best way for the moment.

I may challenge the policies one day but not until I know PQ & BI really really well......

Kind Regards
Suzy