cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors