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!
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.
Using calculated columns in sharepoint list in BI
Hi All, I have a list on sharepoint where i have a start date and an end date, i am using a calculated column to work out how many working days are between the two dates. I then have a hidden column that always has the value 30 in it. The idea is that i multiply the days by 30 to get a total value. This all works. I have 2 issues that i feel are related, one simple and one more complicated (i think). The first: I am wanting to have a total under the "amount to pay" column which holds the days * 30 - there is no option to add a total. Second: I am wanting to use power BI to create some dashboards for this data but there is no option to sum the values that have been created for "total to pay". It seems that this calculated value is not seen as a number that can have maths done to it. My question: How can use a column with a calculated number to get totals and use in power BI to graph?
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
Excel File with Data Model in SharePoint Online
I’ve been working to move a lot of my Excel reports to SharePoint from our file shares and have our users interact with them either directly through Excel online. A lot of my reports contain data that reside in the workbook’s data model (PowerPivot). It is my understanding the SharePoint online does support workbooks with a data model, and in fact, everything seems to work as expected in our test tenant. However; the exact same file does not work in production, and I get an error stating “This workbook contains external data connections or BI features that are not supported” when I attempt to filter the pivot table in anyway. The file that I'm testing with is a basic Excel file, that contains no external data connections, and the pivot table’s data source is a table in the data model. Here are some of the things I’ve tried and verified to this point (our test tenant is first-release enabled): Made sure my test tenant collection and production collection matched on enabled site collection features Same thing as above just for the individual site features In the test tenant, I confirmed that it worked with multiple accounts, some with the free Power BI license applied and others without In the production tenant, I tried with both an account that was synced through AAC and one that was created directly in the Office 365 admin interface. Same error message for both accounts. I’ve tried opening the file in Excel online in multiple sites in both test and production. The results are the same throughout, I can filter the pivot table in test but I can’t in production. Is there a SharePoint setting or a feature that needs to be enabled to allow advanced analytic featues such as a PowerPivot data models to function in Excel Online? Thanks.
