Forum Widgets
Latest Discussions
Power BI Service report on SharePoint lists with multi-select relationships
Dear all, we plan to document our projects on project specific SharePoint sites with risks, decisions, changes, scope statements and status reports in dedicated lists. The idea is to also cross-reference those items, i.e. for example a weekly status report (documented as a row in the list) could reference multiple decisions, risks or changes (rows from the other lists) via lookup columns. In addition to this setup, we want to be able to report via Power BI / Power Query in Power BI Service on the data documented in the project specific SharePoint sites and their lists. Now we are facing several challenges in this setup: dynamic data sources the number of projects, and hence the number of SharePoint sites and their lists are dynamic and need to be queried using functions, to circumvent the dynamic data source problem in Power BI service, e.g. Web.Contents() with option RelativePath extraction of multi-select references using SharePoint.Tables() gives the IDs of the referenced items in the other list, but this method cannot be modified with option RelativePath to circumvent dynamic data sources issue Web.Content() is able to use RelativePath option, however output for multi-select relationships doesn't seem to be complete getting identifiers for resolving references across different list one option we identified is combination of List.Id property (GUID) and ListItem.Id (int32), however here we are not sure if these are unique identifiers another option would be the field UniqueID {32-bit Hex} coming via SharePoint Search api call data privacy levels/combination of different data sources for resolving the relations between list (and related query output) lists would need to be referenced, which is not working in the Power BI service across different queries However we think, that we can circumvent that by doing the/resolving the relations via DAX So all in all i tried to combine several approaches to adress these requirements and also restrictions, however i am not sure anymore if the above outlined approach would be feasible at all. I hope that my description is not to confuse; in case anything is unclear or should be outlined in more detail please let me know. Did anybody else try to address such requirements and succeeded? Looking forward to any input for feedback :) Kind regards Petter
SharePoint Lists and Folders for PBI Report.
For work we have an organizational SharePoint site. Within that site we have a page for each office. I have several reports that I must run each month and I am trying to decide on the best method. 1) Metadata vs SubFolders within a Document Library? I have tried metadata but the metadata field, named "ReportType" doesn't show in Power BI when I connect to "SharePoint Folder". 2) SharePoint folder/file/list cleanup. When I connect to SharePoint there are many fields such as "Author" and "ID" that I am unfamiliar with, I just delete these .. is that the best practice? 3) Once the reports are built, I publish them to a workspace. Is it better, more secure or preferable to build a data flow in the workspace vs withing PBI Desktop? Any other advise? I utilize SharePoint Lists and Folders for work but need to learn the best practices.I'm getting the below error when visualizing a List using the embedded PowerBi. I looked high and lo
I'm getting the below error when visualizing a List using the embedded PowerBi. I looked high and low can couldn't find any information regarding status code 400. A little help, please? Thank you! We're having trouble visualizing your SharePoint list data right now. Activity ID: 18b7b8a7-c865-4bbe-ad55-f11f693d1c94 Request ID: b6fee20b-c8e4-e400-eaf6-6f4e3abec4f9 Correlation ID: be76456b-164a-0f69-752c-d55378453b66 Status code: 400 Time: Wed Nov 22 2023 12:17:55 GMT+0800 (Hong Kong Standard Time) Service version: 13.0.22007.81 Client version: 2311.1.16680-train Cluster URI: https://wabi-north-europe-c-primary-redirect.analysis.windows.net/Retrieve Power BI report name into sharepoint list
I have built a PowerBI dashboard feedback form using Microsoft Forms. I have connected the feedback form to a SharePoint list using Power Automate, so the customer feedback is saved in the SharePoint list. The plan is to create a button on the powerBI dashboards for the customer to click on and fill out the MS form. The only problem I have is that I need help to identify which report they are using when sending this form through. Can I generate the report name somewhere on the form to identify this?
how do i get last week sale for same period when I don't have the dates column
Hi there, I have data in this format: One column has the year. The second column has the period (12 periods in a year, but they are not the same as months, as they start on different dates). The third column has the week number (every period has at least 4 weeks; some of them have 5 weeks). I'm trying to create filters for the data: The first filter is the year, followed by the period and then the week. The data I get after applying these filters is correct. Now, I've tried using the 'sameperiodlastyear' function to retrieve data from the previous year with the same period and week, but it's not working. Can you please help me? Thanks.Tutorial - Tenant-level usage metrics, site/subsite/list/library inventory with Power BI
Hi folks, sharing this here because I think it might be useful to fellow SharePoint admins. Long story short, there's lots of good data in the OData feeds in SharePoint, and you can access those with Power BI. The feeds are site-level, but using a function you can take your query and have it iterate over all of your SharePoint sites to create a tenant-level set of reports and schedule refresh on that in the Power BI service. The sites query has a lot of the same data you'll find in the admin center, so all-time/recent views, last modified date, site template, etc. The value-add here is that Power BI can get the subsites, which you will not see in the admin center. Since we're phasing out subsites, it's useful to get an inventory to see what needs to be replaced/moved/archived. The lists query has item counts as well, which can be good for keeping an eye on things approaching the 5k list view threshold. I did a couple videos on how to get started with this technique here, planning on adding more as time goes by: Get sites and subsites: https://youtu.be/qYhXdNDnyfI Get lists and libraries: https://youtu.be/_MHRH3EbSuk Credit to Jordan Murphy as well, who figured out how to do this and asked me to share the technique. 🙂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?
