SharePoint BI topics

Power BI Service report on SharePoint lists with multi-select relationships

CuriousProjectUser — Mon, 20 Oct 2025 14:32:47 GMT

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

multiple like excel reports in Power BI

AiLearn2008AI — Sat, 19 Oct 2024 17:41:38 GMT

I have a report in Excel that is released once a week. During data cleaning I remove the top 5 rows. how do I automate this for every new document that is dropped into the folder as well as set the standard for all previous versions of the report.

SharePoint Lists and Folders for PBI Report.

AiLearn2008AI — Sun, 08 Sep 2024 12:32:27 GMT

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.

Need export SharePoint integrated Power BI Visualization to excel

Steve_Witter — Fri, 05 Jul 2024 14:06:43 GMT

To be able to pick date picker on a SharePoint List we used power BI integrated visualization. Once that Date picker range is used. need to export to excel. Cannot find a way to achieve. Any solutions.

Tablero para monitorear el uso de almacenamiento de sitios

jeramirez — Mon, 05 Feb 2024 15:29:47 GMT

Hola.

Quisiera saber si hay alguna manera de generar un reporte en power bi desde el cual se pueda monitorear el uso de almacenamiento de todos los sitios de SharePoint de mi dominio.

I'm getting the below error when visualizing a List using the embedded PowerBi. I looked high and lo

925stuart — Wed, 22 Nov 2023 04:20:33 GMT

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

Elijah94 — Wed, 08 Nov 2023 09:59:17 GMT

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

sandeepvig — Fri, 29 Sep 2023 15:12:37 GMT

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

christinepayton — Wed, 27 Sep 2023 15:51:45 GMT

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

Adamhumbug — Fri, 04 Aug 2023 16:08:54 GMT

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?

Automating a Client Information Database with Appointment Info

LaurenM36 — Tue, 01 Aug 2023 23:24:53 GMT

Hey there!

I currently have a sharepoint list to hold my team's client information (CI List) and I'm looking at making one that holds all the info on appointments (i.e. length of appointments, how many, type of appointment etc.) (Appt List).

Not sure if it's important but both of these lists take data straight from respective Forms.

For analysis purposes though, I'm hoping to find a way to compare information across the 2 lists. We use a common client ID for both lists but different information in both, one has demographic info, the other has appt info.

An example of what I'd want to be able to find is how many appts did we have with male vs. female clients or how many of our clients accessing a certain type of appt have disabilities.

I know that I could use the lookup column, but that sounds like an awfully manual way of doing it, with probably hundreds of clients by the time we run the data.

I also know that a work around would be to have my team just fill in the demographic information into the Appt List, but it seems like double-handling since we have that exact info nearby.

I really appreciate any help the community can give me!!
Thank you!

Rolling fiscal year columns

jburnscameron — Mon, 24 Jul 2023 22:15:01 GMT

How can I use a date offset type functionality within a sharepoint list where I need fiscal period columns for -1,0,1 fiscal years? Data is queried into a PBI data model where cumulative data needs to be retained year over year by fiscal year and period. It could be a static table or hardcoded logic used to manually assign the current year as 0 each fiscal. This is an interim solution, but looking for a more practical way of managing rolling over fiscal years without adding 12 new columns each year.

Track Number of Days Item Spends in a Stage

SJCEE — Tue, 20 Jun 2023 13:50:59 GMT

We are using a Sharepoint list to track individual items, each item sits within one of three stages. Each item can move forward a stage as well as backwards.

Is it possible to have a counter that will track the number of days and item spent in each stage?

Thanks,

Sam

Intergrated Report Link?

jamescosten — Mon, 12 Jun 2023 11:03:02 GMT

I have used the quick funstion to create a POWER BI Report from an SP List but i cannot see this report in my POWER BI workspace nor when i try to embed the full link of the report into the site does it work.

How do you embed a generated report you never made?

SharePoint Online - Embed Power BI Reports

TahmidBari — Wed, 24 May 2023 23:30:33 GMT

Hi SPO Team,

I have hundreds of power bi reports that I need to embed into the SharePoint online site. In Azure AD, four security groups have been created, so we can maintain the site access.

Do I need to create a hundred separate SharePoint pages to include those reports? Also, is it necessary to create four separate SPO Teams sites based on the security groups, or can it be done in a single SPO Teams site, so the O365 users can access those reports? Thanks.

Completeness of a product dossier (based on SharePoint)

JoostvdLinden — Thu, 04 May 2023 09:55:08 GMT

Hi all,

I have received this interesting question from one of our customers. They have a Microsoft 365 subscription that includes Microsoft 365 Business Premium and Power BI Pro. They are actively working in SharePoint Online, that's where they store all their data. I think this is a Business Intelligence related question.

They import and export products and per product a standard set of documents should be created, i.e. a technical file, a test report, etc. They sell hundreds of products.

They would like to have a per product overview of how complete the dossier is, and make it measurable.

For example: The documentation for Product X is 75 percent complete, because the following documents are missing:

- X

- Y

- Z

Would it be feasible to generate such an overview in Microsoft 365?

Only thing I could think of is making sure there are content types created in SharePoint Online and leveraging managed metadata search.

Thanks for all help provided!

Connecting Power BI to SharePoint Online list with more than 12 lookup columns

CuriousProjectUser — Mon, 13 Feb 2023 06:54:52 GMT

Dear all,

for consolidating different data sources in a Power BI report I need to connect to a SharePoint Online list, which currently includes more than 12 lookup-table based columns (mainly people picker).

Using the standard SharePoint.Tables connector in Power Query fails due to the general 12-lookup-based-fields-threshold in SharePoint.

Do you have any ideas how to approach this issue? An idea of mine would be to use the same approach as within SharePoint with distinct views only displaying part of the entire list scope and merging the views in Power Query, however, I don't know how to connect Power Query queries to individual views of SharePoint lists. Any ideas on that?

On the other hand: is there a way to connect Power Query to a SharePoint API and directly request individual columns of a list?

Looking forward to your input (even some exhaustive documentation)!

Kind regards

Petter

Enable Print but Restrict Download for PDFs in SharePoint Online

Vidit_Gholam — Fri, 03 Feb 2023 04:44:47 GMT

Hi All,

We have a group of users who have view only or restricted view permissions on SharePoint online , we wanted these users to be able to print the PDFs but not download or bulk download them, is this possible.

I have tried Information Right Management, Didn't work for me as I expected.

Unable to filter for multiple values in column

Acorn999 — Thu, 26 Jan 2023 23:04:42 GMT

I am working in Power BI. I have Premium Capacity.

Source Data is Excel.

The source column has multiple values separated by semicolon.

This shows my scenario from left to right:

I am not able to return accurate results from the slicer if mulitple values are selected because the filter context only looks for a single value.

I have tried:

1] Create a separate table for the slicer default values, create relationship to source data table with both one to one and many to one cardinality

> the issue is the graphs from Population only show data where the column has a single value. It won't show the data where the column had multiple values or shows them all combined in their own bar.

2] create separate columns for Population so each value has its own column

> the issue is the filter only looks at the first column. It can caused missed data if the value was in columns 2,3,4,5

There should be a way to deal with columns that have multiple values separated by semicolon, show only unique values in the slicer, but filter context needs to pass and return rows where any one or more slicer filter values is found in the one column in any order.

how to export a sharepoint online power bi integrated report?

childishbambino — Mon, 23 Jan 2023 16:37:27 GMT

Is there really no way to take a dashboard youve made with sharepoint online power bi integration and export it to be used in desktop or even other SPO lists??????