M Query Transformations on separate and Appended Tables

[Anonymous]

Hi, I have two questions related to appended tables

1) I have a data model in my report where I append three Excel Workbooks. I do my transformations on each table, then Append them and continue additional Transformations with the tables combined.
two of the tables each have under 1000 records; while the third has 250,000 records and getting larger by 30K each month.

My question is, is it better to do as many M Query transformation on separate tables and THEN combine them, or first append them and THEN do the transformations on the Appended verstion?
Which is the more efficient way to do it and/or speed up the reports? 

I have this option to one or the other or both, and was curious of which would be more efficient.
And are there any web sites that go into these kind of details?

2) With these appended tables, I currently don't have them connected to the data model. They are separated and i take the appended table and relate the appended, to another table.

Is it generally better to join all tables of an appended table, or leave them disconnected and then create the relationship using the appended table only?

Thanks!

[Anonymous]

Thanks! your response was very helpful!

[Anonymous · Accepted solution]

Hi @Anonymous ,

1) Normally, it is better to make some transformations(like remove unnecessary columns, delete the rows, filter the rows etc) for every query first to get a "clean" and "thin" query. Then combine these queries. You can refer the following official documentation when work with Power Query. 

Best practices when working with Power Query

Combine or Append Data: Optimal Combination Pattern

And for the third has 250,000 records and getting larger by 30K each month, you can consider configuring incremental refresh for it.

Configure incremental refresh and real-time data

All You Need to Know About the Incremental Refresh in Power BI: Load Changes Only

2) And whether need to create any relationship with the other tables in the model for these appended tables, it depends on your actual scenario and requirement. It is OK even though you don't create any relationship for them with other tables in the model... You can provide some sample data in your model tables and your expected result. Later we can give you some suggestion on it base on your shared specific examples. Thank you. 

Any comment or problem, please feel free to let me know.

Best Regards