Summarize of all my replies:
M code attached.
- Table_Source is a table example created to illustrate your scenario: embedded table created from a group.
- Then some transformations in the embedded table:
- removing an embedded column
- adding an embedded index
- transfering a value from the outside into embedded table
- Processing multiple transformations in the embedded table using both embedded and non embedded column
- 3 is the answer to your question
- 4 shows what else you can do with embedded tables. If you understand the logics, you can do almost all that you need.
- Note that you can sometimes replace the (e)=> by each (it may be easier to undertand) , but in some cases the
(e)=> is mandatory like here:
AddQtyPercentageinEmbeddedTable = Table.AddColumn(#"AddColumnCaptionInEmbeddedTable", "Qty Pct", (e) => e[Qty]/o[Total Qty], Percentage.Type),
Good luck
let
TABLE_SOURCE= let
Source = Table.FromColumns( {{"A","A","A","A","B","B","B","B","C","C","C","C"}, {"1/1/2018","2/1/2018", "3/1/2018", "4/1/2018", "1/1/2018","2/1/2018", "3/1/2018", "4/1/2018","1/1/2018","2/1/2018", "3/1/2018", "4/1/2018"}, {10,12,14,16,30,32,34,36,60,62,64,66},{"caption1", "caption2", "caption2", "caption1", "caption3", "caption3", "caption3", "caption4", "caption4", "caption4", "caption4", "caption4"}}, {"Ref", "Date", "Qty", "Caption"}),
ChType = Table.TransformColumnTypes(#"Source", {{"Date", type date}, {"Ref", type text}, {"Caption", type text}, {"Qty", Int64.Type}}),
Group = Table.Group(ChType, {"Ref", "Caption"}, {
{"Count Rows", each Table.RowCount(_), Int64.Type},
{"Total Qty", each List.Sum([Qty]), Int64.Type},
{"Data", each _, type table}
})
in #"Group",
// Processing transformations in the embedded table
Source = #"TABLE_SOURCE",
// removing one embedded column
RemoveEmbeddedColumn = Table.TransformColumns(#"Source", {"Data", each Table.RemoveColumns(_, {"Caption"}), type table}),
// adding an embedded index
AddEmbeddedIndexColumn = Table.TransformColumns(#"RemoveEmbeddedColumn", {"Data", each Table.AddIndexColumn(_, "Embedded Index",1,1), type table}),
// Transfer external caption into embedded Table
AddEmbeddedColumn = Table.AddColumn(
#"AddEmbeddedIndexColumn",
"Data Amended",
(o)=>Table.AddColumn(
o[Data],
"Embedded Caption",
each o[Caption],
type text
),
type table
),
// Transfer external caption into embedded Table
// adding mulptiple steps
// with e=> instead of each
// "e" stands for "Embedded" columns, and "o" stands for "Outside" columns
AddEmbeddedColumn2 = Table.AddColumn(
#"AddEmbeddedIndexColumn",
"Data Amended",
(o) => let
Source = o[Data],
AddColumnCaptionInEmbeddedTable = Table.AddColumn(#"Source", "Embedded Caption", (e) => o[Caption], type text),
AddQtyPercentageinEmbeddedTable = Table.AddColumn(#"AddColumnCaptionInEmbeddedTable", "Qty Pct", (e) => e[Qty]/o[Total Qty], Percentage.Type),
AddColumnTotalQtyInEmbeddedTable = Table.AddColumn(#"AddQtyPercentageinEmbeddedTable", "Embedded Total Qty", (e) => o[Total Qty], Int64.Type)
in #"AddColumnTotalQtyInEmbeddedTable",
type table
),
// replace Column DATA by Column "Data amended with new external column"
RemCol = Table.RemoveColumns(#"AddEmbeddedColumn2" ,{"Data"}),
RenCol = Table.RenameColumns(#"RemCol", {{"Data Amended", "Data"}})
in #"RenCol"
and some other examples of embedded transformations
// SelectRows in embedded table
Table.TransformColumns(#"Table Name", {"Data", each Table.SelectRows(_, each [embeded table Column1]<>"filter1" and [embeded table Column1]<>"filter2"), type table}),
// SelectColumns in embedded table
Table.TransformColumns(#"Table Name", {"Data", each Table.SelectColumns(_, {"Column1", "Column2", "Column3"}), type table}),
// Table.Distinct in embedded table
Table.TransformColumns(#"Table Name", {"Data", each Table.Distinct(_), type table}),
//CombineColumns in embedded table
Table.TransformColumns(#"Table Name", {"Data", each Table.CombineColumns(_, {"Column1", "Column2", "Column3"}, Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None), "New Combined Column")})
Add column(s) to nested Tables
