Here's my scenario
I have a spreadsheet that is stored on a shared drive, this spreadsheet is updated fairly regularly.
What I want to do, is generate a PowerBI report that pulls specific data from cells and rearranges them in a format of my choosing.
Example: Workbook has plenty of data that we don't need for the report. Only need data from two rows: ROW 2 and ROW 7. ROW 2 contains City names, and ROW 7 contains staff names.
I want to generate a report that looks like this:
| City | Staff |
|---|---|
| L2 (ROW 2) | L7 (ROW 7) |
| M2 | M7 |
CodePudding user response：
This is a solution that works with OneDrive. Not sure where you shared data resides??
Yes, I am doing this currently from OneDrive. I can be kinda a pain in the butt to get this working. Go to your OneDive speadsheet and open it in excel desktop (Open in app). Then do File, Info, copy path. For example,
http://url-string/path-to-xl/contacts.xlsx?web=1
remove '?web=1' from the string.
In PowerBi, use GetData icon, search for Web in the list and select. Then paste in the URL from above so that your PowerBi project can use the data. Make sure that the file permissions on OneDrive are correct or you might receive error messages.