Merging Queries with Sharepoint Folders Connected

Question

Hi Power Bi Community,I have an employee listing that shows whether the employee is still employed if they are on this listing. Because it is in cycles i.e 300,&nbsp; I have created a sharepoint folder so that it gets uploaded automatically. I have then grouped it by employee ID and make a rank so it only shows the latest cycle for each employee ID.&nbsp; From there, I am looking at years of timesheet data to try and match the ID to the cycle that the employee last was in. Every time I try and merge the query, it provides me with the first cycle of the sharepoint folder.&nbsp;Would love some help with this! Thank you&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
It seems like you are trying to merge the query in Power BI Desktop to match the employee ID with the latest cycle. To fix the issue of getting the first cycle instead of the latest, you can try the following steps:

In the Power Query Editor, sort the SharePoint folder data by cycle in descending order.
Group the data by employee ID, and in the aggregation, use the "All Rows" operation.
Add a custom column to extract the first row of the "All Rows" column for each group. This will give you the latest cycle for each employee ID.
Expand the custom column to get the desired columns from the latest cycle.
Merge the query with the years of timesheet data using the employee ID.

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Merging Queries with Sharepoint Folders Connected

1 Reply

Related content

OData Error When Connecting to SharePoint Online

Sharepoint Connection

Connecting to sharepoint

Merge data from multiple excel files from multiple sharepoint folders

Connecting Sharepoint Folder to PowerBI