Table insert / rows to group

[Dicken]

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 

[ZhangKun · Accepted solution]

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

[Dicken]

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

Richard. 

[ZhangKun · Accepted solution]

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

[Dicken]

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 · Accepted solution]

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