Excel import - replacing and adding to data

[Helen_1]

Hello. 

I will be working with a daily report of customer data that has transactions 'unposted', 'current' or 'expired'. 

I have connected my Power BI report to a share point folder that will automatically receive an excel file from an email, daily.

Unposted transactions are subject to change, the posted and expired transactions are not.

I am planning to break the customer info into two reports coming from the business software into Sharepoint, one report for new posted and expired transactions that will stay in the share point folder and one report for all unposted transactions that I automate to be replaced daily.

Is there a way that I can use one report from the business software to share point daily, with all transactions that have changed status that day or are unposted, and have Power BI replace old transactions with new transactions by transaction number. Not all data will be replaced, only if it is included in the daily report. 

Can you please explain how I set-up the connection to sharepoint and have Power query replace old data with new data by transaction number.

Thanks in advance. 

[Anonymous · Accepted solution]

Hi @Helen_1 ,

In Power BI Desktop, use the "Get Data" feature to connect to SharePoint. Provide the URL of the SharePoint folder where your transaction reports are stored. Select the Excel file you want to connect to and import the data into Power BI.
Create a report on a SharePoint List in Power BI Desktop - Power BI | Microsoft Learn
Once the data is loaded into Power Query, you will need to apply transformation steps to identify and replace old transactions. Use the "Merge Queries" feature to join the new data with the existing dataset on the transaction number field. Apply a filter to keep only the most recent transactions based on your criteria (e.g., status change or 'unposted'). Remove duplicates by keeping the latest entry for each transaction number.
Merge queries overview - Power Query | Microsoft Learn
After publishing your report to the Power BI service, you'll want to set up a scheduled refresh to ensure your data stays up-to-date with the SharePoint folder. Configure refresh settings in the dataset settings within the Power BI service to refresh daily, matching the frequency of your SharePoint updates.
Configure scheduled refresh - Power BI | Microsoft Learn

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