Dynamically add new table from a refreshed table to an existing one using Power Query or DAX

[thiagowsousa]

Hi experts,

I would like to have one table that will be dynamically populated by another table that will have the same number of rows but the data will be different each day I get a refresh.

But the populate process cannot erase the previous data that was appended before.

So, it would be something like the following:

Day 1:

Table 1: data source

Day 1, 1, 0

Day 1, 2, 1

Day 1, 3, a

Table 2: getting data from table 1

Day 1, 1, 0

Day 1, 2, 1

Day 1, 3, a

Day 2:

Table 1: data source

Day 2, 4, b

Day 2, 5, c

Day 2, 6, d

Table 2: getting data from table 1

Day 1, 1, 0

Day 1, 2, 1

Day 1, 3, a

Day 2, 4, b

Day 2, 5, c

Day 2, 6, d

Does anyone know how can I create a data model to automatically do this append in a refresh process, i.e., one table getting data and populating another data as new data every time I have a refresh being executed?

Thanks in advance.

Regards,

Thiago

[parry2k]

@thiagowsousa you can use Append  in power query

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[thiagowsousa]

hi @parry2k,

Thanks for the help. If I use Append queries, the final result dynamically increase the number of rows in the table 2?

In summary:

Day 1:

> table 1 - 5 rows

> table 2 - 5 rows (from table 1)

Day 2:

> table 1 - 5 rows

> table 2 - 10 rows (5 from the Day 1 and 5 more from day 2)

Day 3:

> table 1 - 5 rows

> table 2 - 15 rows (10 from the previous days and 5 from day 3)

...

I thought Append Queries would only put table 1 (fixed) below table 2 and not dynamically appending the tables as the days change the data in table 1.

is my understanding correct?

Thanks again.

Regards,

Thiago

[parry2k]

@thiagowsousa not really sure what actually you are trying to do if table 2 contains all the data, why you need table 1, provide some more realistic example, what are your files, and how is the data? it is hard to follow with your examples. At least I'm not able to follow, I hope someone else can help.

[thiagowsousa]

Hi @parry2k ,

what I need to do is to collect one set of rows from a website that display data for the current date only and take this set of rows to a table. This process should repeat day after day always getting a set of rows for the current date and adding new rows to the table which will contain data from the previous date.

it is something like creating a history table from a collection of several queries in different dates.

my objective is to have this done automatically. So, let's say, in a scheduled refresh, Power BI should run this query to extract data from the web and add it to an existing table with previous data.

Is is clear now?

thanks in advance.

regards,

thiago

[v-zhenbw-msft]

Hi @thiagowsousa ,

We find a way that might help you.

Get two different data source in Power BI Desktop, one is excel, another is folder.

Table 1 data in excel, and Table 2 data in excel from a folder.

1. We connect the excel data source using Table 1.

Then we connect the folder data source Table 2.

2. If we update the Table 1 in Excel, we need to copy it and add it to the folder. We need to change the name, so as not to be replaced.

3. The result like this, when we update the excel data, then we copy it to folder, the folder data will be appended automatically.

Hope this helps.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[thiagowsousa]

hi @v-zhenbw-msft thank you very much for the help! The idea is quite promising!

Do you see a way of implementing this idea but changing the excel file for a website and the folder for a SharePoint site?

And also, creating the file from the website, copying it and renaming it in the SharePoint site automatically?

Thanks again!

Regards,

Thiago