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

[TxSteve]

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.

[parry2k · Accepted solution]

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