Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors