Grouping rows in a table with a variable number of columns

[markd]

Hi,

I have a couple of tables with a variable number of columns.  Each row is a set of numerical data (counts of process execution) for an hour of the day on a date. There's over 170 columns, one for each of the different processes.  A column only exists in the table if the process was executed.  The two tables contain data for the same time period and (mostly) the same processes.  I've appended the two tables together into a single table and now want to group by date and hour (first two columns).  I just want to sum all the other columns with matching data rows to get the total number of process executions of each type for each hour of each day.

I came across a really good solution for programmatically changing table column types in this forum here.

Is it possible to adapt this solution to work with Table.Group? 

Cheers,

Mark.

[markd]

Hi,

I have a couple of tables with a variable number of columns.  Each row is a set of numerical data (counts of process execution) for an hour of the day on a date. There's over 170 columns, one for each of the different processes.  A column only exists in the table if the process was executed.  The two tables contain data for the same time period and (mostly) the same processes.  I've appended the two tables together into a single table and now want to group by date and hour (first two columns).  I just want to sum all the other columns with matching data rows to get the total number of process executions of each type for each hour of each day.

I came across a really good solution for programmatically changing table column types in this forum here.

Is it possible to adapt this solution to work with Table.Group? 

Cheers,

Mark.

[v-sihou-msft]

@markd

To group on two columns, you just need to apply a set with those two columns in Table.Group() function. You can directly do it in Query Editor.

The Power Query can be like:

let
    Source = Sql.Databases("servername"),
    AdventureWorksDW2014 = Source{[Name="AdventureWorksDW2014"]}[Data],
    dbo_FactInternetSales = AdventureWorksDW2014{[Schema="dbo",Item="FactInternetSales"]}[Data],
    #"Grouped Rows" = Table.Group(dbo_FactInternetSales, {"CurrencyKey", "SalesTerritoryKey"}, {{"Column", each List.Sum([SalesAmount]), type number}})
in
    #"Grouped Rows"

However, if you need to keep all other columns in your table, you have to create one aggregated column for each of them.

Regards,

[markd]

I should have probably been clearer about the "variable number of columns" that I'm working with.  I'm working with two sets of data that I get each week (two tables).  The list of columns in the tables can be (and often is) different each week.  I was looking for a way to combine the data in the two tables without having to manually write a table.Group() function with around 170 columns being summed...   The structure of the table group function implies that it should be possible to programmatically construct table.Group() arguments programmatically from the list of columns to be summed in much the same way as is shown in the other discussion thread that I linked to.   I've got 90% of the way there, but I'm stumped as to how to convert the arguments from text into a type that Power query will treat as execuable code. 

[CahabaData]

try this: select a Matrix visual (is next to the Table visual) ... and then in the visual design pane for the rows bin; drag & drop your date and hour (first two columns)..... then put all the other fields as values.