Power query total table

[Abvlecxe]

Hi, I have 10 tables which represents 10 regions all have individual files, the tables are the same in structure but obviously the values relate to each region.


I have created connections for each file and then appended these to make one query table which is easy enough. However, I now need a group position which is all these 10 tables added together and so i am looking for suggestions as to the best most efficient way to do this?


I need to be able to see the individual regions within my final table but within this table i also need to see the total of all the regions added together for each row of the individual tables.

I thought about adding in rows within the appended table to basically add the required rows above, although i can't see the ability to add rows in query only columns.


My next thought, i have already created an appended table with all 10 tables, i then thought about creating a merged table of the individual tables and then appending this new table to my 1st appended table which would then show all the individual regions as well as a group position on the one table, is this possible?

This is not feeding into power BI nor Power Pivot otherwise i would have just created mesures etc to give me this.


Any other / better ideas would be appreciated.

Thanks

 

[sandy666]

if I understand well you want total table like: data1+total row1, data2+total row2, etc...?

so try transpose/sum cols/transpose for each table then append all tables but imho this way create table not well formed

better is just add ID column for each table, eg. Region1, Region2.... then Append all tables

 

[Ron Coderre]

Let's see if this gives you something to work with.
Using this sample data in an Excel Table named Table1:

Name      Month    Amount
Alpha       Jan       878
Bravo       Feb       527
Charlie     Mar       312
Delta       Apr       259
Echo        May       230
Foxtrot     Jan       247
Alpha       Jan       382
Bravo       Feb       314
Charlie     Mar       555
Delta       Apr       356
Echo        May       728
Foxtrot     Jan       797
Alpha       Jan       157
Bravo       Feb       879
Charlie     Mar       351
Delta       Apr       606
Echo        May       388
Foxtrot     Jan       601
Alpha       Apr       407
Bravo       May       909

Create a blank query and open the Advanced Editor for it.
Copy this M-Code and paste it into that window.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Month", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Month"}, {{"All", each _, type table}, {"Group Total", each List.Sum([Amount]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Name", "Month"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Name", "Month", "Amount"}, {"Name", "Month", "Amount"})
in
    #"Expanded All"

Click Done to save the new code and view the results:

Name       Month    Amount   Group Total
Alpha       Jan       878       1417
Alpha       Jan       382       1417
Alpha       Jan       157       1417
Bravo       Feb       527       1720
Bravo       Feb       314       1720
Bravo       Feb       879       1720
Charlie     Mar       312       1218
Charlie     Mar       555       1218
Charlie     Mar       351       1218
Delta       Apr       259       1221
Delta       Apr       356       1221
Delta       Apr       606       1221
Echo        May       230       1346
Echo        May       728       1346
Echo        May       388       1346
Foxtrot     Jan       247       1645
Foxtrot     Jan       797       1645
Foxtrot     Jan       601       1645
Alpha       Apr       407        407
Bravo       May       909        909

Is that something you can work with?

 

[Abvlecxe]

Thanks for all the suggestions folks, what i have gone with is:

> create 10 query connections
> append these into one table connection
> duplicate the appended table, group the rows in query to give a consolidated table connection
> finally append my first table with the new consolidated table to give me exacly what i need

There is maybe a better more efficient way but this i working out well so far

Thanks folks