Connection between PowerBi and SharePoint

[Switto]

Hi Everyone,

I have made a connection between Sharepoint lists and PowerBI, however, the problem is I have 18 lists and overall 50K line item altogether and it is increasing day by day. Each list has a different column counts ranging from 30- 70. I am confused as to how to build the data model out of it.

Thanks 

[v-lionel-msft]

Hi @Switto ,

"the problem is I have 18 lists and overall 50K line item altogether and it is increasing day by day."

You mean each list is a table, and the number of lists is increasing every day, and each list contains a lot of data, right?

My recommendation is to remove as much useless data as possible.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[trebgatte · Accepted solution]

What's the question(s) you are trying to answer? Let's start there. Otherwise, you are attempting to create BI content by throwing data at the problem. While the artist Jackson Pollack was able to do this, I've found it isn't an effective BI Creation strategy. Starting with the question will result in you doing a lot less work.

Once you understand where you are going, then you can identify the data that is needed. Data generally is classified as either facts or dimensions. Facts are the things that happened, like purchases, promotions, hiring, task updates, etc. Dimensions are the things that the actions to which the actions were applied. For example, you may have clients, projects, stores, etc.

It is ideal to have one fact table, with multiple dimension tables related to it in a one to many fashion. These dimensions represent the pivots of the data. This is what is known as a star schema. 

I hope this is enough to get you started. 

--Treb, Power BI MVP

Check out my Power BI blog posts at https://marqueeinsights.com/category/power-bi/

[Switto]

@trebgatte Thanks for the explanation. So, we are working on the Productivity tracker which has 18 processes which translates to 18 lists. Each list has different columns, which is required for that process and each list has thousands of rows as well. 

I am thinking to remove the columns which are not required and trying to match the columns from all the list and build a data model. I hope it works.

@v-lionel-msftThanks for the tip. I am removing the not required columns.