Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.