Showing results for 
Search instead for 
Did you mean: 
New Member

Create a table where each row summarises column data from another table

I have a function that accepts a table and a column name and returns some information on that column. I currently return a list but it could be a record [value1=v1, value2=v2, .... ]either if that was better for this question.

fn = (table, col) => ....etc... in { v1, v2, v3, v4} 


I would like to create a summary or reporting table with a row for each column name in the original table and columns value1, value2, ... valueN


I know from @Lars Schreiber how to use the aggregate functions to add these columns to a Table.Profile output. That's fine, but I would like to learn how in general to create a second table from some analysis of the source table columns. Procedurally, I would do it by creating an empty table with the output columns and then populate each row corresponding to each source column. But I'm finding it hard to switch into the M functional thinking mode.


What is the technique there please?







New Member

Daniel, I'm afraid  I don't understand the meaning of "col" in that. I want to use a column name, but List.Transform expects a list as the first argument. Both of the two "result" lines here give a table of "Error" for the two columns.


fn=(tbl,col)=>#table(Table.ColumnNames(tbl),{List.Transform(col,each List.Sum(Table.Column(tbl,_)))}),
col="First Column",
tbl = #table({"First Column", "Second Column"}, {{1,2},{3,4}}),
result = fn(tbl,"First Column")
//result = fn(tbl,Table.Column(tbl,"First Column"))
in result

I'd better give an example. Suppose I have a fn that returns a list of three items from a given list of values.


col="First Column",
tbl = #table({"First Column", "Second Column"}, {{1,2},{1,4},{5,3}}),
result = fn(Table.Column(tbl,col))
in result
/* the fn gives me a list for ONE column's list of values:


I would like an expression which calls fn for each column in the table and returns a Table with one row for each source column and as many columns as values in the returned summary list, like this:
ColumnName       Count First Last
First Column         3        1       5
Second Column    3        2       3


If it helps I could write fn as






Super User
Super User

fn=(tbl,col)=>#table(Table.ColumnNames(tbl),{List.Transform(col,each List.Sum(Table.Column(tbl,_)))}

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors