How to link the Task table of multiple Sharepoint Task Lists

[Matt22365]

Hi

I hope someone can help.

I have a number of task lists designed for different teams to record what they are working on. Each list generates a unique task ID number per line. All of the lists are in the same PWA site.

What i would like to do is link all of the Task tables from all of the lists so we see, in BI, a consolidated table with all that information in, is this possible?

Thanks for your help

Matt 

[v-lili6-msft]

hi, @Matt22365 

If you want this: import all Sharepoint Task Lists, then create the relationship with consolidated table,

and then use RLS to for different teams.

https://docs.microsoft.com/en-us/power-bi/desktop-rls

Best Regards,

Lin

[v-lili6-msft]

hi, @Matt22365 

After my research, I'm afraid it couldn't achieve in Power BI for now.

For your requirement, it needs you to pull all the tables from the different task lists.

Best Regards,

Lin

[Matt22365]

Hi @v-lili6-msft 

Thank you for looking into this for me. That is a shame, I will, eventually, have about 20 sharepoint task lists which all need linking so will make the relationships complex.

I am struggling to link the 2 lists which I have active, Ive tried to make a relationship using the ID column but when visualised the 2 tables don't merge. Do you have any idea why this is happening?

Thanks for your help

Matt 

[v-lili6-msft]

hi, @Matt22365 

What is "when visualized the 2 tables don't merge" mean? Could you share some screenshots for it and what is your expected output. if you had created the relationship correctly. If possible, please share some simple sample data and expected.

Best Regards,
Lin

[Matt22365]

Hi

So Ive imported 2 sharepoint task lists and created a relationship between the 2 tables. Ive tried a few options for the relationship, the screenshot shows one of the options Id - Id.

Now these are linked I hoped that any visual I create would use data from both fields, however the data seems to be staying independent. The below screen shot shows a table which should merge all the request numbers from the ACF and DR tables (data should include TL-ACF-2019_XX and TL-DR-2019_XX), however, the merge isnt happening and I am only seeing data from one of the tables, depening on which table I take the values from

I have also attempted to build a bridging table between the 2 data sets using the team name (calculated column to give each table an individual team name), screenshot below. This also did not give me a merged table

 

Any help would be greatly appreciated

Thanks

Matt 

[v-lili6-msft · Accepted solution]

hi, @Matt22365 

What you want "merge" should be append function.

https://support.office.com/en-ie/article/append-queries-power-query-e42ca582-4f62-4a43-b37f-99e2b2a4...

http://radacad.com/append-vs-merge-in-power-bi-and-power-query

Best Regards,
Lin

[Matt22365]

Hi

Perfect, thank you, append worked perfectly. Just a shame this cannot be done without having a copy of all the appended tables individually in the file

Thanks

Matt 

[Matt22365]

Hi @v-lili6-msft 

Thanks for your reponse, however this still requires that I pull all the tables from the different task lists. I was hoping for solution similar to what you can do with files in the same sharepoint folder, i.e. Combine Binaries

Is this possible with task lists?

Thanks

Matt