cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sysmod
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?

 

Thanks,

Patrick

 

 

 

2 REPLIES 2
sysmod
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.

 

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

 

let
fn=(listofvalues)=>{List.Count(listofvalues),List.First(listofvalues),List.Last(listofvalues)},
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:
List
3
1
5

 

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

fn=(listofvalues)=>[Count=List.Count(listofvalues),First=List.First(listofvalues),Last=List.Last(listofvalues)],


*/

 

 

 

wdx223_Daniel
Super User
Super User

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors