Results 1 to 5 of 5

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •