Power Query or Macro to Aggregate Tables

[gitmeto]

I am in the process of considering options to consolidate data from multiple tables. The tables have all the same structure (column labels and number of columns). In total there are 31 tables that need to be aggregated. However, although the structure is consistent, the data is indexed by date and there is variability as to the start date for each table. I thought that PQ would be the answer but it seems as though it will only aggregate data that is consistent or matching, meaning if the dates do not match it will not aggregate the data. Unless there is a way to aggregate data, my other option would be to run a macro, index each table by the date and grab the info to populate a table.

Is there a way to aggregate data that have different number of rows, or does this follow general matrix rules, same number of rows and same number of columns?

 

[severynm]

Are your 31 tables to be appended together to form one large table, or are you trying to merge the 31 tables together based on some criteria? PQ will have no problem combining tables of differing lengths.

 

[gitmeto]

Thanks for responding @severynm .

I have thought about appending them together. I haven't had much luck with merging them. It only constructs a table based on the dates that match. But in the end, I would want everything aggregated into a single date for all the tables but that is something I could try.

As far as merging, maybe there is a setting that I am not configuring when I attempt to merge? There are several options on how to merge. What would you suggest?

Is there a performance difference between merging vs. appending? Does one use less memory that the other?

 

[severynm]

So for your scenario, I'm not entirely sure what you are trying to achieve, but since you mentioned that all of your tables have the same columns, appending them together would make much more sense. Merging is typically done when you have tables with different column information that you would want to combine together for some reason - in other words, appending only adds more rows, and merging could add rows and/or columns, depending.

Yes, you probably won't have much luck with merging, especially if each of your tables have much different dates you are merging against. You should probably try appending. After you append all of the tables, you are left with a single table with the one main date column you could use.

 

[gitmeto]

Thanks @severynm . My goal is to create a single table, were data from all of the data is aggregated into a single table. My only thought is that for the dates that match on each of the individual tables, for example, Table 1 and Table 2 have data from today, but when I append them there will be two rows in appendage, one data set from each table. But I want to combine the data and have a single entry in the appended table rather than two.

Is this possible with M code?

 

[severynm]

Oh ok, so you do want to merge the tables. Some of the tables have duplicate dates that you want to combine together. That is absolutely possible - take a look through the documentation for merging queries in PQ: Merge queries overview - Power Query

 

[gitmeto]

Ufortuantely this only merges the dates that match

 

[severynm]

Did you specify a correct join type?

 

[gitmeto]

I did. I chose Full outer which should have grabbed all rows according to the link.

 

[severynm]

Not sure how much more I can help with unless you'd like to post some sample data from your tables.