Use Power BI with SharePoint Lists for "people" column data, clickable links to forms & more!
Вставка
- Опубліковано 14 жов 2024
- This is a detailed tutorial covering how to deal with multi-value fields, creating hyperlinks directly to SharePoint item records, calculating whether items are overdue, and more.
Here is the concatenation formula we used in a calculated column the table: Stakeholder Names = CALCULATE(CONCATENATEX(Stakeholders, Stakeholders[Stakeholder Name], ", "))
If you're using the SharePoint list to manage requests, you may like this article that goes into detail on the topic: christine-payt...
⏲️ TIMESTAMPS
0:28 Why SharePoint
3:30 Connecting to the list data
5:00 Cleaning the data
6:34 Getting people profile data
8:45 Dealing with multi-value fields
10:51 Hyperlinking to the SP item record
15:16 Relating our multi-value dimension
15:52 Creating visuals
16:49 Changing the date format
17:31 Configuring the item hyperlink
18:30 More visuals
19:38 Custom-sorting text axis labels
22:55 Date table
24:26 Creating a button to link to the "new item" form in SP
26:46 Calculating the "overdue" record count
🛠️ RESOURCES
Blog article on the v2 vs v1 SharePoint Online List connector: christine-payt...
▶️ RELATED VIDEOS
How to create a SharePoint list to act as a form: • How to Make a Form in ...
How to embed a Power BI report in SharePoint: • Embed Power BI Visuals...
🪽 CONNECT WITH ME
Blog: christine-payt...
LinkedIn: / christinehpayton
🎁 SUPPORT THIS CHANNEL
Shop: shop.bi-ome.com/
Followed this video until you completely skipped over what I was looking for to finish off my BI :( I’m here for how you combined the “stakeholder” names into a custom column (and comma separated). That process isn’t clear from your explanation - do you have a video of this?
Yeah, I had a recording blip and so I had added the info in an on-screen text - it's a calculated column, the formula was Stakeholder Names = CALCULATE(CONCATENATEX(Stakeholders, Stakeholders[Stakeholder Name], ", ")). You can add that on your main table and it'll concatenate the values from the related table (make sure you have the relationship from the earlier section). There's not a non-convoluted way to do it from Power Query or via the UI, the DAX calc column is the easiest method--
CONCATENATEX is an iterator, meaning it iterates over your main list table and concatenates the related values in the stakeholders table. You keep the two tables separate and relate them on your item ID - this lets you use the individual names as filters in slicers.
@@bi-ome Wow I didn't expect such a quick response on this! Thanks for the description! I saw another video linked below and there's a nice demo of you doing the formula :) Amazing video and super clear steps to follow. This is something I've been struggling with for months, and finally following your process got my data looking super organized and easy to manage.
@@alit7846 aw thanks!!
@@bi-ome I see that @alit7846 says you have another video of you doing the formula. I can’t find this video. Can you please help me locate it? Thx for the content I am a beginner! :)
@@bi-ome hi! @alit7846 commented that you linked another video where you demo using that formula. I don’t see it can you please point me in the direction?
Ace video, thanks. I'm experienced with SharePoint, but a complete PowerBI novice. This video has bridged the gap for me (although I may be back to it a few times before it fully sinks in!)
Thank you so much for this video! It really helped a colleague and I solve an issue at work, that was time-sensitive. Life saver!!! Subscribing now!
Thanks for this tut. The added part I'm trying to understand is how we can track & log when status' change and/or modified by a stakeholder and when. E.g. if one of those records went from "Pending", "In-Process", "Completed", i could see the Journey of A) Who the stakeholder was that changed it B) the timestamp.
If you want to log every change, you would need to create a Power Automate flow that triggers on modifications to that field in the list item that creates an item in a different list to store the data you want to track. If you do this, make sure to also log the original item ID as a field so that you can relate the change history data back to the original item. It looks like Reza has a video that kind of fits but isn't quite doing exactly this - but it gives you an idea of where to start: ua-cam.com/video/Ek4oYWPWfT0/v-deo.html
@@bi-ome That is a great idea, thank you
Amazing!! Thank you so much! I immediately subscribed. Any tips on how to consolidate 10 separate SharePoint list into one powerbi report? I have a Teams site with 10 private channels and each channel has a SharePoint list with identical columns and I would love a central view for myself and my VP. Thanks again!!
Thanks! If you duplicate the query, you should be able to go to the source step for each and edit what it’s connecting to. Then append them all together in a new query to combine if the fields are the same (if you need to identify which is from which site, you can add a custom column to each with a text label, just make the name the same).
Do any transforms/expansions on the appended query do that you only have to do them once vs on each.
Thank you so much Christine. It was a big help!
Thank you so much for posting this, it allowed me to report on the people columns I needed - much appreciated :)
Thanks for rhis tutorial, you mentioned a method to make snapshot (I assume incremental refresh) i a later video. Has this been released?
Yes, it's not using the incremental refresh, it's just a Power Automate flow that captures the state of the data, which you can then connect to in PBI with the SP Folder connector: ua-cam.com/video/RBx-HbVpWTQ/v-deo.html
Supposedly incremental refresh does work with SP sources, though, so that would be interesting to try!
Thank you so much for this video, I could finish my dashboard.
BTW, if you're looking for a SP Online List v2 vs v1 connector comparison, it looks like there is no longer a 5k row limit on the 2.0 connector so I'd go with 2.0 since it's faster. Verified with more info in a blog article here: christine-payton.com/sharepoint-v2-connector/
Nice vidéo! you will really help me for my 1st dashboard!!
Hi Very nice informative steps.
Question -Suppose In people's SharePoint table you have multiple people names, when you expand in Power query it creates multiple rows.
How can expand with only 1 rows even if column contains multiple names
This is a "thing" for any multivalue data from any source - what you typically want to do is create a separate query with just the ID column and the multivalue expanded column, then relate the two tables on the ID to create a dimension table - so you don't expand it in your primary table. There's also an option to expand and just comma-separate the values in a single cell, too, but if you do that you won't be able to filter or count the values, that's why we put it in its own table. There's an example of how to handle it in this video (the source here isn't SP, but it is multivalue people data): ua-cam.com/video/eEmpzueZfqY/v-deo.html
Good day Christine.
Thank you for this video. I have been using Sharepoint List for some time. If you could, I have list files over 20k lines. Connector API2.0 will not connect to the list over 5k rows. What would you recommend, noting the following. Connector API 1.0 will take around 1 hr to update online. (18k rows), multiple list files from the same site.
What I have done, Multiple connectors to the same List, using date sort, download the old data into different queries. Then I leave only one query active for the latest data. In BI, I set the queries not to refresh, Then I create a Union query to connect the different List files together to make up a Fact table for reporting. Do you have any other recommendations?
It shouldn’t be taking that long unless you have a ton of lookup/people columns- try putting your “remove other columns” step first at the beginning of the query and/or putting the initial data pull into a dataflow and then connecting to that as many times as you need. Dataflows are great for front-loading all the processing. If I recall, sorting is particularly resource-intensive so I would skip that unless you actually find it helping.
Thank you so much for the video. Maybe it is me I tried to see but it was so far away.
I know, sorry! I can't fix it after the fact, perhaps someday I will re-record. I have started using UI zoom on the newer videos so that they are easier to read. I haven't been doing this very long, still learning ^^
Hey!
Thanks for the video - I'd have a quick question here: Do you also know how to deal with open text field data type export from MS Lists to Power BI?
When I open the table in Power Query after importing it from MS Lists, the values of the open text field column are not only the actual text I inputed in the MS list but also some code like formatting etc within a " " element.
I would appreciate your help - thanks in advance!
Yeah, that is the rich text markup. The best way to avoid it is to make the column plain text from the start (it’s in column settings), but changing the type after the fact won’t remove it if it’s already there I don’t think. You can do a replace step to replace the characters, but it’s kind of a pain.
Thanks for your quick response!@@bi-ome Yea I will try to edit the column type in MS Lists straight away!
Awesome thanks!
Is there a tutorial you have that displays how you concatenated the Stakeholders? I tried using you custom column formula, but I was receiving an error message (doesn't recognize the Calculate text).
You'll want to replace your column and table name in the calculation if you're using it from the example - so:
Stakeholder Names = CALCULATE(CONCATENATEX(, [stakeholder name column], ", "))
You also need a relationship between the stakeholders table and whatever your primary table is for this to work, and if it's 1:many set that relationship to bidirectional. You can do it without the relationship if you want to, but the DAX would be different - and you want the relationship so you can filter on stakeholder if you want to.
Basically, CONCATENATEX is an iterator, meaning it iterates over a table and does whatever thing you are wanting it to do - so in this case, it's iterating over stakeholders and comma-separating whatever is in the name column, and using the relationship to decide which values belong to which rows. You want CONCATENATEX and not CONCATENATE.
learn.microsoft.com/en-us/dax/concatenatex-function-dax
is the data in power BI updated live? or there is an time interval which i need to set and where i can set it? How can I link the power BI report in the sharepoint list so that when people access the sharepoint list can view the report as well at the same time? Thanks.
It depends on your source settings. SharePoint data isn’t live, but pro licensing can schedule 8 refreshes per day so pretty close. You do this in data source settings after publishing.
If you want a really seamless interactive experience with both on the same page it’d probably be best to go with the Power Apps visual in the report, but changes wouldn’t be live. You can search for “power bi write back” in UA-cam for tutorials if it sounds interesting. You could also use the Power BI web part and a list view web part on the same page in SP, but they aren’t as “aware” of each other that way - just displayed side by side.
in Shp i'm using 'people' format for column to update name (multi field)
But i'm getting Table instead of List option.
Also how to use above in Appended table
I would just try expanding it and see what happens, assuming you have an expansion icon in the column header. It'll work fine on appended tables as long as the data structure of the tables you're appending is the same (e.g. column names and types). Do the append step before the expansion step.
@@bi-ome append steps after expansion
when we select the appended table data , the name value shows blank
maybe because we are creating a separate table
@@sandeshkhilari1990 You want to do the append step before the expansion, not after, otherwise it won't expand the column on the appended rows--
What if you are using people picker and you only have ID numbers showing in the field in power bi
There’s two versions of the people fields in the query editor, one that has the people records and one that has the IDs - I would scroll through and look for the other. The view tab has a columns search too so you could search on column name-
Thank you. I found the Column🎉
Great!
Hey thanks for the video. I have sharepoint list in which I have date column but when I pull this list data into power bi, the date is showing one day before the sharepoint list date column. Pls suggest solution
The date on the back end is stored in UTC. You can use something like this to change the time zone: community.fabric.microsoft.com/t5/Desktop/Convert-utc-to-local-time-zone-using-Power-Query/m-p/45533