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

[nileshwh]

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 ID	Dept Name
1	IT
2	HR
3	Finance

EmployeeDetails

Employee ID	Employee Name	Dept ID
100	John	1
200	Linda	2
101	Steve	1
201	Ramesh	2
102	Raj	1

LocationDetails

Dept Location Country	Dept Location State	Dept ID
US	New York	1
UK	London	2
India	Delhi	1
Germany	Berlin	2

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

Dept ID	Dept Name	Employee ID	Employee Name	Employee ID	Employee Name	Employee ID	Employee Name	Dept Location Country	Dept Location State	Dept Location Country	Dept Location State
1	IT	100	John	101	Steve	102	Raj	US	New York	India	Delhi
2	HR	200	Linda	201	Ramesh			UK	London	Germany	Berlin
3	Finance

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

[V-lianl-msft]

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.

[nileshwh]

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]

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

[nileshwh]

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).