How to add rows with date and other aggregation data daily to a table.

[LynnW]

Every day I will get a total cost summary like this:

1. Day 1 - 09/15/2025

Total cost summary:

Category	Cost
Fruit	120
Vegetable	200
Meat	350

After day 1, I want to have a table:

Date	Category	Cost
09/15/2025	Fruit	120
09/15/2025	Vegetable	200
09/15/2025	Meat	350

2. Day-2 09/16/2025

Total cost summary:

Category	Cost
Fruit	100
Vegetable	250
Meat	200

After day 2, I want to have a table:

Date	Category	Cost
09/15/2025	Fruit	120
09/15/2025	Vegetable	200
09/15/2025	Meat	350
09/16/2025	Fruit	100
09/16/2025	Vegetable	250
09/16/2025	Meat	200

3. Day 3 09/17/2025

Total cost summary:

Category	Cost
Fruit	150
Vegetable	220
Meat	300

After day 1, I want to have a table:

Date	Category	Cost
09/15/2025	Fruit	120
09/15/2025	Vegetable	200
09/15/2025	Meat	350
09/16/2025	Fruit	100
09/16/2025	Vegetable	250
09/16/2025	Meat	200
09/17/2025	Fruit	150
09/17/2025	Vegetable	220
09/17/2025	Meat	300

Question: How can I add new rows to table with date?

[MasonMA · Accepted solution]

@LynnW 

HI, Assuming your daily cost summary comes as a new file each day (Excel or CSV), the easiest way to build the table you want is to use the Folder connector in Power BI.

You can put all of the daily files in a single folder. In Power BI, go to Get Data > Folder and connect to that folder. Use the Combine option so Power Query pulls all of the files together. Add a Date column so that you can take it from the file name if it’s included there, or from a field inside the file.

[v-achippa]

Hi @LynnW,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Mauro89, @GeraldGEmerick, @MasonMA and @Ashish_Mathur for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @LynnW,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[LynnW]

Thanks for following up with me about this issue, it has been solved. Thx.

[MasonMA · Accepted solution]

@LynnW 

HI, Assuming your daily cost summary comes as a new file each day (Excel or CSV), the easiest way to build the table you want is to use the Folder connector in Power BI.

You can put all of the daily files in a single folder. In Power BI, go to Get Data > Folder and connect to that folder. Use the Combine option so Power Query pulls all of the files together. Add a Date column so that you can take it from the file name if it’s included there, or from a field inside the file.

[LynnW]

Here is the automatically step I want to do:

1. Download daily summary into my folder - I have figured using power automate.

2. Once the new file is created, automatically append to my datasource with curret adding time. - By using folder connector, I'm able to get the table I want, but have to do it manually everyday. Not sure there is any other way I can make it automatically.

3. My line chart automatically updates based on the new data.

[Ashish_Mathur]

Hi,

Follow the steps shown in this video - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube

[MasonMA · Accepted solution]

If you’re already using the Folder connector, you don’t need to manually append files each day. Power BI will pick up any new file in that folder, the key is in how you refresh the dataset:

In Power BI Desktop you have to click Refresh manually. That’s why it feels like you’re doing it every day.

In Power BI Service set up a scheduled refresh. For example, run it every morning at 7 AM. Power BI will automatically re-read the folder, pull in any new files, and append them into your dataset. Your line chart will update without you touching it.

Since you’re already automating the file download with Power Automate, all you need is the scheduled refresh in the Service.

[GeraldGEmerick]

@LynnW You would typically do this with something like a Folder query.

[Mauro89]

Hi @LynnW,

What you want to build is a history / append table — instead of replacing yesterday’s numbers, each new day’s summary gets added as new rows.

A few ways to do it:

1. Power Query (manual load)

• When loading the daily summary, add a custom column with the current date.
• Use Append Queries to stack the new day’s data on top of the existing table.
• This requires reloading all data each time, unless you stage it elsewhere.

2. Dataflows Gen2 or Pipelines (recommended for automation)

• Land each day’s summary in a Lakehouse or Warehouse table.
• Configure the pipeline/dataflow to append instead of overwrite. This way, every run inserts a new batch with the date included.

3. SQL-based approach (if using a Warehouse)

• Create a staging table for the daily file.
• Use INSERT INTO HistoryTable SELECT <today’s date>, Category, Cost FROM StagingTable.

Power BI itself won’t “add rows daily” — you’ll need to design the load (Power Query, Dataflow, or Pipeline) to append the new day’s file into your storage.

Best regards!

Feel free considering the post as solution if it helps you.

[LynnW]

I want to make a automation process. Is there any way I could utilize .csv files in my OneDrive folder? Can 'power automate' append the new report records to datasource?

[phyberoptx]

Yes, but you don't need PowerAutomate. Instead, open the OneDrive folder online. Get the path before "_layouts." Then in Power BI Desktop, use the Sharepoint connector. Add a folder path filter to your query. You'll probably want to also filter against file type (.csv or .xlsx depending on your data). Here's a helpful youtube short: Connect to OneDrive and SharePoint.