Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
1 year ago
Solved

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 ...
  • ZhangKun's avatar
    1 year ago

    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
  • ZhangKun's avatar
    ZhangKun
    1 year ago

    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