cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Rookie Question -New Table / New Append to Query / Or Other - For multiple SharePoint Lists

LISTS:

I have 11 SharePoint (SPO) 1-List is considered the Master. Primary ID field.
There are 10-Lists with a matching KEY field.  

Several Designer Workflows work behind the scenes to sync the data.

PROCESS:

A user creates a new task in the Primary list, they select teams to assign the task too.

POWER BI:

I use ODATA to connect to the Master list due to the excessive fields/workflows. I use the SPO Connector to connect to the sub-lists. I can see all of my data.

My challenge is the relationships. The lists don’t join with ID and KEY fields. Technically I can connect 3 or 4 but connecting beyond that seems to error with a many to many relationships.

QUESTION:

Being new to Power BI and reporting, how should I proceed? I’ve read online about using the Append Query as new, then select all 10 sub-lists and chose all the fields I want to create one new list.  Then it appears I create 1 connection between my master list and the one query. Is that correct or should I investigate another approach.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VII
Super User VII

Re: Rookie Question -New Table / New Append to Query / Or Other - For multiple SharePoint Lists

@TxSteve yes as best approach, you should append all the 10 lists (child lists) and it will become one big table and then link it with your master table. I would use this approach.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

1 REPLY 1
Highlighted
Super User VII
Super User VII

Re: Rookie Question -New Table / New Append to Query / Or Other - For multiple SharePoint Lists

@TxSteve yes as best approach, you should append all the 10 lists (child lists) and it will become one big table and then link it with your master table. I would use this approach.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors