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

Need to create report using the data from multiple SharePoint Lists in Single Row

Hi

I want to create the report using power BI. The report should be created using the data from multiple sharepoint lists. Please find the  lists and data relations between these lists. DeptID is reference in both lists(EmployeeDetails and LocationDetails)

 

DeptDetails

Dept IDDept Name
1IT
2HR
3Finance

 

EmployeeDetails

Employee IDEmployee NameDept ID
100John1
200Linda2
101Steve1
201Ramesh2
102Raj1

 

LocationDetails

Dept Location Country Dept Location StateDept ID
USNew York1
UKLondon2
IndiaDelhi1
GermanyBerlin2

 

The data should be diplayed in the following format which I can then export in excel file

 

Dept IDDept NameEmployee IDEmployee NameEmployee IDEmployee NameEmployee IDEmployee NameDept Location Country Dept Location StateDept Location Country Dept Location State
1IT100John101Steve102RajUSNew YorkIndiaDelhi
2HR200Linda201Ramesh  UKLondonGermanyBerlin
3Finance          

 

So please suggest how this report will be generated using Power BI

 

4 REPLIES 4
V-lianl-msft
Community Support
Community Support

According to my attempt, this format is difficult to implement unless the table is split into many tables.

Placing Employee ID & Employee Name columns in these tables into the visual can get a similar format.

Can you provide some more details or sample using data provided in my query? So I will get more clearity to work on this report

aj1973
Community Champion
Community Champion

Hi @nileshwh 

How did you create the lists in sharepoint? there is a specific way to do it. 

If the files are already in excel, why don't you pout them in a folder and use Sharepoint Folder to improt them inot Power bi desktop. the rest is easy

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

nileshwh
Frequent Visitor

Hi Amine,

 

Ther SharePoint lists have been already created and crud operations get performed using powerApps. Now I want to generate Power BI report to display the data spead in diffferent SP list in single row deptwise. The report data I want to import in excel file with download functionality. But the biggest challenge I am facing is  displaying the employee details and Location details in single row in different columns for each department (As shown in screenshots).

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!