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

Question

HiI want to create the report using power BI. The report should be created using the data from multiple sharepoint lists. Please find the&nbsp; lists and data relations between these lists. DeptID is reference in both lists(EmployeeDetails and LocationDetails)&nbsp;DeptDetailsDept IDDept Name1IT2HR3Finance&nbsp;EmployeeDetailsEmployee IDEmployee NameDept ID100John1200Linda2101Steve1201Ramesh2102Raj1&nbsp;LocationDetailsDept Location Country&nbsp;Dept Location StateDept IDUSNew York1UKLondon2IndiaDelhi1GermanyBerlin2&nbsp;The data should be diplayed in the following format which I can then export in excel file&nbsp;Dept IDDept NameEmployee IDEmployee NameEmployee IDEmployee NameEmployee IDEmployee NameDept Location Country&nbsp;Dept Location StateDept Location Country&nbsp;Dept Location State1IT100John101Steve102RajUSNew YorkIndiaDelhi2HR200Linda201Ramesh&nbsp;&nbsp;UKLondonGermanyBerlin3Finance&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;So please suggest how this report will be generated using Power BI&nbsp;

aj1973 · Answer

Hi&nbsp;nileshwh&nbsp;How did you create the lists in sharepoint? there is a specific way to do it.&nbsp;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 · Answer

Hi Amine,&nbsp;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&nbsp; displaying the employee details and Location details in single row in different columns for each department (As shown in screenshots).&nbsp;

v-lianl-msft · Answer

According to my attempt, this format is difficult to implement unless the table is split into many tables.
Placing Employee&nbsp;ID &amp;&nbsp;Employee&nbsp;Name&nbsp;columns in these tables into the visual can get a similar format.

nileshwh · Answer

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

Forum Discussion

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

4 Replies

Related content

Merge data from multiple excel files from multiple sharepoint folders

How to combine SharePoint List from multiple SharePoint sites

Creating multiple Audiences in PowerBI App

Sharepoint list mirroring

Multiple messages when new item created

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

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

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