Use Power BI with SharePoint Lists for "people" column data, clickable links to forms & more!

[@alit7846]

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?

[@bi-ome]

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.

[@alit7846]

@@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.

[@bi-ome]

@@alit7846 aw thanks!!

[@samantham7765]

@@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! :)

[@samantham7765]

⁠​⁠@@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?

[@wmcnabb]

Christine - this is EXACTLY what I needed today! Thank you very much for detailing an awesome technique. The only think I will need to get my users to do is refresh the Power BI after the updates.

[@bi-ome]

Sure! You can schedule refresh on this too. I can’t remember if I described that process in this video or not, but I have another video on scheduling refresh (it’s on files - but list sources are refreshed the exact same way).

[@wmcnabb]

@@bi-ome Yes for sure we can schedule those refreshes...

[@NeilThomas-f7q]

I am creating a change control system using SPO lists and fronting it with PBi (I'm a novice at the latter) this video is going to save me days and weeks of effort (Thank you)

[@bi-ome]

@@NeilThomas-f7q I am working on a few more that would probably be relevant to you right now too! They will be ready after the holidays. SP lists for tracking things is one of my favs

[@bi-ome]

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/

[@mge415]

Thanks "again" Christine another "Task Done !" Thanks you and your kindness for sharing your knowledge :)

[@bi-ome]

You are so welcome!

[@HIGHL4NDER]

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!)

[@OutofCompton]

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!

[@freder1ckisme]

Thank you so much for posting this, it allowed me to report on the people columns I needed - much appreciated :)

[@EpinionatedMusic]

Thank you so much Christine. It was a big help!

[@keinthf]

Thank you so much for this video, I could finish my dashboard.

[@roberttyler2861]

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.

[@bi-ome]

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: kzbin.info/www/bejne/e5yXoIyNhbyZipI

[@roberttyler2861]

@@bi-ome That is a great idea, thank you

[@lape36]

Thanks for rhis tutorial, you mentioned a method to make snapshot (I assume incremental refresh) i a later video. Has this been released?

[@bi-ome]

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: kzbin.info/www/bejne/iHPbXnuYi9WKirM Supposedly incremental refresh does work with SP sources, though, so that would be interesting to try!

[@stephaneblas4004]

Nice vidéo! you will really help me for my 1st dashboard!!

[@healthsis1768]

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!!

[@bi-ome]

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.

[@RTSWatson]

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?

[@bi-ome]

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.

[@AmanShaikh-bl9qi]

Genius.... absolutely genius.

[@magdalenaauer7178]

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!

[@bi-ome]

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.

[@magdalenaauer7178]

Thanks for your quick response!@@bi-ome Yea I will try to edit the column type in MS Lists straight away!

[@sandeshkhilari1990]

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

[@bi-ome]

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): kzbin.info/www/bejne/m3bQoa2rmr-Zp7s

[@chrisdobson9381]

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).

[@bi-ome]

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

[@shanyliew3252]

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.

[@bi-ome]

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 KZbin 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.

[@EllieCarter5225]

Thank you so much for the video. Maybe it is me I tried to see but it was so far away.

[@bi-ome]

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 ^^

[@sandeshkhilari1990]

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

[@bi-ome]

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.

[@sandeshkhilari1990]

@@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

[@bi-ome]

@@sandeshkhilari1990 You want to do the append step before the expansion, not after, otherwise it won't expand the column on the appended rows--

[@pavelandreev6023]

Awesome thanks!

[@tattoogrl208me6]

What if you are using people picker and you only have ID numbers showing in the field in power bi

[@bi-ome]

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-

[@tattoogrl208me6]

Thank you. I found the Column🎉

[@ChristianPecksteiner]

Great!