Forum Widgets
Latest Discussions
Excel Services and On-Premises SQL Data
I am looking to have an active data connection from an Excel file in Excel Services on SharePoint Online (O365) that has a data connection to an on-premises SQL database. Need to be able to have data refresh work like it does with SharePoint on-premises. The DMG used for PowerBI doesn't appear to have connections for Excel. I am looking for "out-of-the-box" solution (or close to one). Is this still a supported scenario? It was widely used in on-premises SharePoint system as is currently blocking some key Office 365 adoption. Thanks!
New feature: Create Power BI reports from tables in Excel files and CSV files
We are excited to announce a new feature that will make it very easy to start visualizing data in Excel tables and CSV files using Power BI. When you go to your OneDrive for Business, or any SharePoint document library, you will be able to select an Excel file that contains tables, or a CSV file, and click on "Open in Power BI" in the command bar. Clicking this command will open a new tab in your browser and take you to Power BI. A Power BI dataset will be automatically created based on your Excel or CSV file and you can start creating a report to visualize your data. Note that this requires that your data is formatted as tables in Excel. The dataset created in Power BI will be automatically refreshed when you make changes to the original Excel or CSV file. This means you can continue to make changes to the file in OneDrive or SharePoint, and any associated dataset, reports and dashboards will be automatically updated with the latest data. Refresh happens every hour, but if you want to see changes right away you can start a refresh in Power BI by clicking the ellipses beside the dataset, then clicking the refresh now icon. The dataset will be added to your group's workspace if the SharePoint site you started from has a group association (like a modern team site). Otherwise, it will be added to your personal workspace. Any user can create a report based on an Excel or CSV file in Power BI for free, but to share the report with others you will need a Power BI Pro license. If the user creating the dataset already has a Power BI Pro license, they will be able to take full advantage of it. If you are a first time Power BI user, you may find this page helpful for an introduction. We will be gradually rolling this feature out to customers over the coming weeks and months, and as always listen to your feedback. We will make updates to this post as the feature rollout expands through our release process.Can't see people's name when reporting a SharePoint List in a Power BI report
I created a column in my SharePoint List called "Category Manager", and populated with people's names in the company. After creating a Power BI report using this List as data source, I can only see numbers in the Category Manager column, not names. I tried different lists with different report, the numbers corresponding to same person aren't the same. So it looks like the numbers aren't from my company's employee database. What is it then? How do I get the report to show people's name other than a number? ThanksHow to get SharePoint Library metadata / custom columns in Excel via PowerQuery?
When I use the "Export in Excel" option from a SharePoint library, this creates a query file with the iqy extension. This can be saved as an odc connection for future refreshes, but either way I'd rather handle everything via Power Query rather than mix PQ with "old school" data connections. The Excel table built via the iqy file or resulting odc connection (through Data > Existing Connections) includes extra columns that we added to the library However using PQ to retrieve a SharePoint folder only returns default columns: > From SharePoint Folder I also had a look at results returned by the SharePoint Lists and Odata feed connectors, and they return way more than what I need (and I'm not sure they have what I do need, I'd rather mess around with these if I don't have to). Is there a way to get the same results via Power Query that you get from "Export in Excel" as detailed above? Any help appreciated. This is with Excel 2016 (up-to-date O365 ProPlus monthly release) and SharePoint Online. I haven't tried from PBI Desktop though I'd expect the same behavior.
Group by view not showing all groups
Hello, I have a document library with multiple tags. One of the fields is a dropdown with 3 items. When I do a 'group by' using the dropdown field I can only see groups for 2 items. The 3rd one is just invisible. I have documents tagged to all three options. So all the 3 tags are in use. When I ungroup them they are all visible. Any help will be appreciated.
Issue powerbi getting data from SharePoint Search
For a Client of mine i am trying to create a dashboard of all documents in a sitecollection. it was no problem with odata.feed. but problem with that was that i can not schedule it to refresh. i finally used web.contents. to refresh the data this works but now i have the XML data as it does not want to read the Json of SharePoint i am in a nested table issue. I worked a lot out and came down to this and here is my problem: Table.FromRows(List.Transform(element1, each List.Transform(_, each Record.FieldValues(_{1}){0} ))), the problem is that record.field values only returns 1 column instead of all my columns. does anybody have a solution so it shows all columns? I am new to the Powerbi stuff so have tried a lot but syntax incorrect or did not work :-( hope for some answers. Kr, Paul
