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

Подписаться 9 тыс.

Просмотров 22 тыс.

50% 1

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/

Опубликовано:

14 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 46

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

@HIGHL4NDER 2 месяца назад

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 8 месяцев назад

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!

@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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Ek4oYWPWfT0.html

@roberttyler2861 Год назад

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

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

@EpinionatedMusic 2 месяца назад

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

@freder1ckisme 5 месяцев назад

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

@lape36 10 месяцев назад

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 10 месяцев назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-RBx-HbVpWTQ.html Supposedly incremental refresh does work with SP sources, though, so that would be interesting to try!

@keinthf 7 месяцев назад

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

@bi-ome 8 месяцев назад

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/

@stephaneblas4004 7 месяцев назад

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

@sandeshkhilari1990 5 месяцев назад

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 5 месяцев назад

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): ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-eEmpzueZfqY.html

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

@EllieCarter5225 26 дней назад

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

@bi-ome 26 дней назад

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

@magdalenaauer7178 10 месяцев назад

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 10 месяцев назад

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 10 месяцев назад

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

@pavelandreev6023 11 месяцев назад

Awesome thanks!

@chrisdobson9381 7 месяцев назад

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 7 месяцев назад

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 5 месяцев назад

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 5 месяцев назад

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 RU-vid 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.

@sandeshkhilari1990 4 месяца назад

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 4 месяца назад

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 4 месяца назад

@@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 4 месяца назад

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

@tattoogrl208me6 6 месяцев назад

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

@bi-ome 6 месяцев назад

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 6 месяцев назад

Thank you. I found the Column🎉

@ChristianPecksteiner 6 месяцев назад

Great!

@komailbutt2998 Год назад

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

@bi-ome Год назад

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