cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markd
Regular Visitor

Grouping rows in a table with a variable number of columns

 

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.

3 REPLIES 3
CahabaData Solution Sage
Solution Sage

Re: Grouping rows in a table with a variable number of columns

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.

 

www.CahabaData.com
Microsoft
Microsoft

Re: Grouping rows in a table with a variable number of columns

@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.

 

55.PNG

 

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
Regular Visitor

Re: Grouping rows in a table with a variable number of columns

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. 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors