Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to 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
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
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,
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