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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Dicken
Post Patron
Post Patron

Table insert / rows to group

Hi,

I have found something , I want to insert  sub total to a grouped table, fine, but then I wanted to add add column 
and use the total as a perecent denominator, but when I added a column the 'grand total' that I had added using 
Table insert rows was not included, now this is great in this situation, but what if i did want all the rows; 

I can do a small mock up but this is what I have ; 

 

Table.Group(Source, {"Name"}, {{"Count", each

                                            let ttotal = [Item ="Gtotal", Name ="", Unit = List.Sum( [Unit] ) ] ,

                                                inserttable = Table.InsertRows( _, Table.RowCount(_), {ttotal} )

                                              in
                                                Table.AddColumn( inserttable, "Pcent", (x)=> [Unit] ) }} )


I think the problem is it is refering to the   each _   table  at the beginning of the grouping, not the inside added row 
table, is there a way around this?  I've tried naming the variables but end up with something quite messy. 

Richard 

2 ACCEPTED SOLUTIONS
ZhangKun
Super User
Super User

You are right to use x as the variable name. If you still use the each keyword (_ as the parameter name), then the internal [Unit] only represents the value of the [Unit] column of each row in the inserttable table.

 

let
    Source = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmOlWB0IzwiITcA8J6icKZxnBFfpBGaB5EE8ZxSVziimOENVQnguUJUInhEKD2GmC1gcJB8bCwA=", BinaryEncoding.Base64), Compression.Deflate)), {"Name", "Item", "Unit"}), {{"Name", type text}, {"Item", type text}, {"Unit", type number}}),
	result = 
		Table.Group(Source, {"Name"}, 
			{{"Count", 
				each
					let 
						gt = List.Sum([Unit]), 
						ttotal = [Item ="Gtotal", Name ="", Unit = gt],
						inserttable = Table.InsertRows(_, Table.RowCount(_), {ttotal})
					in
						// It works, but it's not concise enough 
						// Table.AddColumn(inserttable, "GroupPcent", (x) => x[Unit] / List.Sum([Unit]))
						Table.AddColumn(inserttable, "GroupPcent", (x) => x[Unit] / gt)
			}} 
		)
in
    result

View solution in original post

First you should read this part:

https://learn.microsoft.com/en-us/powerquery-m/m-spec-functions#simplified-declarations

I think you should know that each keyword is a syntactical alternative to "(_) =>".

 

Then you need to understand this part:

https://learn.microsoft.com/en-us/powerquery-m/m-spec-consolidated-grammar#field-access-expressions

When there is no variable (identifier) ​​or record structure before [], an implicit method (that is, looking for a variable named _) is used to find the record (or table column). The official method is called implicit-target-field-selection.

 

Finally, you need to understand the problem of environments, which allows you to understand nested structures.

https://learn.microsoft.com/en-us/powerquery-m/m-spec-basic-concepts#environments-and-variables

 

View solution in original post

4 REPLIES 4
Dicken
Post Patron
Post Patron

Thanks, I'll accept as solution, I have not had a propoer chance to go through it, 
but it looks good. 

Richard. 

First you should read this part:

https://learn.microsoft.com/en-us/powerquery-m/m-spec-functions#simplified-declarations

I think you should know that each keyword is a syntactical alternative to "(_) =>".

 

Then you need to understand this part:

https://learn.microsoft.com/en-us/powerquery-m/m-spec-consolidated-grammar#field-access-expressions

When there is no variable (identifier) ​​or record structure before [], an implicit method (that is, looking for a variable named _) is used to find the record (or table column). The official method is called implicit-target-field-selection.

 

Finally, you need to understand the problem of environments, which allows you to understand nested structures.

https://learn.microsoft.com/en-us/powerquery-m/m-spec-basic-concepts#environments-and-variables

 

Well that should keep my occupied for a while, very often it's knowing  what infromation  to look for, 
i remember finding  I could select columns using  [[ ]]  but took me ages to find out it was called projection so did not know what to search for. 
Thanks, 

ZhangKun
Super User
Super User

You are right to use x as the variable name. If you still use the each keyword (_ as the parameter name), then the internal [Unit] only represents the value of the [Unit] column of each row in the inserttable table.

 

let
    Source = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmOlWB0IzwiITcA8J6icKZxnBFfpBGaB5EE8ZxSVziimOENVQnguUJUInhEKD2GmC1gcJB8bCwA=", BinaryEncoding.Base64), Compression.Deflate)), {"Name", "Item", "Unit"}), {{"Name", type text}, {"Item", type text}, {"Unit", type number}}),
	result = 
		Table.Group(Source, {"Name"}, 
			{{"Count", 
				each
					let 
						gt = List.Sum([Unit]), 
						ttotal = [Item ="Gtotal", Name ="", Unit = gt],
						inserttable = Table.InsertRows(_, Table.RowCount(_), {ttotal})
					in
						// It works, but it's not concise enough 
						// Table.AddColumn(inserttable, "GroupPcent", (x) => x[Unit] / List.Sum([Unit]))
						Table.AddColumn(inserttable, "GroupPcent", (x) => x[Unit] / gt)
			}} 
		)
in
    result

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors