Hello all
I’m trying to add a row with “total” in the description column, but it has to be the last row.
I guess i could do it with append but can we do it with M?
Thanks a lot
Pedro
Hi @pedroccamara,
Give this method a go, just paste this M code in a new blank query.
let
MyTable = Table.FromRecords({
[CustomerID = 1, Name = "Bob", Description = "Some text"],
[CustomerID = 2, Name = "Jim", Description = "Other text"]
}),
NewTable = Table.FromColumns(
Table.ToColumns( Table.RemoveColumns( MyTable,{"Description"})) & {MyTable[Description] & {"Total"}},
Table.ColumnNames( Table.RemoveColumns( MyTable,{"Description"})) & {"Description"}
)
in
NewTable
I hope this is helpful
1 Like
@pedroccamara Do you want to add “Total” in the description column plus a sum for the values, Pedro?
I once saw something similar to this:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Added Total" = Table.AddColumn(#"Transposed Table", "Custom", each if [Column1] = "Description" then "Total" else
try List.Sum(List.RemoveFirstN(Record.ToList(_),1)) otherwise null),
#"Transposed Back" = Table.Transpose(#"Added Total"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Back", [PromoteAllScalars=true])
in
#"Promoted Headers"
Works fine if you have any number of text fields and you want to sum up all the other columns (s. example). If you only want certain columns to be summed up, select with an if statement similar to the “Description” condition e.g. columns with Value/Sales/Volumes/…
Example:
2 Likes