Creating a new table in Power BI

[Anonymous]

Hi everyone,

Right now, I am working on a huge datesets. So I have different tables, but I do have a table with the most details in it, and let's call it A. Now, I have updated some new tables and want to merge or append into the table A. I can do it very easily in query editor, however, I found it is not the fastest way to do it, it could takes houly to generate the new table. 

So I am trying to do in two ways,

First, I am trying to write Dax to create new tables like using union or other functions to create a new table.

Second, I am going to use R to do the stuffs, as I feel like it's easy to write in R than in Dax. 

I am not sure which way will make the process quick, as I am still trying to figure out the Dax code.

Please comment anything you have thought about or you have the same problem with me.

Thanks

[Anonymous]

@Anonymous,

Writing custom SQL is your best bet. Both DAX and M code utilizes your computer memory, so writing custom SQL would let the server handle all the heavy lifting

[Anonymous]

That could be a good try, but what happened if the data are not from the same service, or we just upload all the file into the same service and then write a good sql to get the data we want?

[Anonymous]

Are you trying to do Merge functionality or APPEND functionality in DAX?

If MERGE, how many columns you want to bring from Table 2 to Table A?

Thansk
Raj

[Anonymous]

Now I have 3 tables, my goal is to get a table with all the values in it.

A

B

C

[Anonymous]

Hi

1. Table A and C should be UNIONed ( or APPENDed)  as firts step. Doing the APPEND is the easiest way, but DAX offers UNION function as well. UNION ( Table_A, Table_C). Not sure abt the performance though.

2. Once you are done with the UNION, Use the RELATE function to bring in 'DATE' filed from Table_B.

Hope this helps.

Thanks
Raj

[Anonymous]

Hi Raj,

THanks for the help, yes that is what I did, I post the question to ask if there is any other ways to improve the performance, because our data is kind big and it takes hours to get the final table, and I feel like doing this could cost more time, as you have to make the relation each time. 

So I am trying to use one Dax function to finish all the work or I could do it in R.

Thanks

[Anonymous · Accepted solution]

Hi

If possible, i would do this in database level as doing this in PBI will hit the performance when the data is huge.

But, lets see if we get any other ideas in this forum.

Thanks
Raj

[Anonymous]

Sorry , I add duplicated photos, but you can figure out which one, and the idea is there