Forum Discussion
Resolver IAdd multiple calculated Columns INSIDE Nested Table without add Custom Column outside, in Power Quer
Solution SageYou can either do the formula directly in the AddCustomColumn or just create a custom function and call the custom function in either the Table.Group step or a TransformColumns after the grouping.
You can add the (add_columns) part as a custom function or just copy the formula from "let" to "in add_multiple_columns" directly in a Table.AddColumn.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsmvzC9JVNJRMgRhAyBhoGepFKuDJGMExKbYJIxBkli1mIBksOoBCRoj6/HIz0uB2W8KkTBDkTACOwybDMh+Q2NsMiD7DRGmxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Period = _t, Warranty = _t, #"WarrantyCalled After n periods" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Period", Int64.Type}, {"Warranty", Int64.Type}, {"WarrantyCalled After n periods", type number}}),
add_columns = (tbl as table) =>
let
add_index = Table.AddIndexColumn(tbl, "Index",0),
rows = List.Buffer(Table.ToRecords(add_index)),
add_multiple_cols =
Table.ExpandRecordColumn(
Table.AddColumn(add_index, "Custom", each
[
ColumnA = try rows{[Index]-1}[Warranty] * rows{[Index]-1}[WarrantyCalled After n periods] otherwise null,
ColumnB = try rows{[Index]-2}[Warranty] * rows{[Index]-2}[WarrantyCalled After n periods] otherwise null,
ColumnC = try rows{[Index]-3}[Warranty] * rows{[Index]-2}[WarrantyCalled After n periods] otherwise null
]
),
"Custom",
{"ColumnA", "ColumnB", "ColumnC"}
)
in
add_multiple_cols
,
#"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"NestedTable", each add_columns(_), type table [Category=nullable text, Period=nullable number, Warranty=nullable number, WarrantyCalled After n periods=nullable number]}})
in
#"Grouped Rows"
Resolver I
Question to
Thank you spinfuzer, I appreciate your time and advise.
I am new to Power query Syntaxes, Micosoft help site idoes not help in certain codes. I do not understand certain codes you use "
Table.ExpandRecordColumn(
I will search.
Question Can a Variable move with the Row Index:
Int=AllDataTable[Warranty]{[Index]} if yes, Does the Int AllDataTable{[Index]}[Warranty] which one is correct?
or I have to write : Int1=AllDataTable[Warranty] and in separate line
Int=Int1{[Index]}
I simulated my real case to Cartable, but I wrote the following code and fine tune it, but still prefer to use
one Variable: Int=AllDataTable[Warranty]{[Index]} and use it in subsequent codes but I want it to change with the {index] value, is it possible?
I have used the following: but I will study your code first to understand the line (Before the function).
#"Grouped Rows" = Table.Group(SortedRows, {"Case", "CC", "Product"}, {{"FullTable", each _, type table}}),
Col.IndexSub = Table.TransformColumns(#"Grouped Rows",{{"FullTable", each Table.AddIndexColumn(_,"IndexSub") }} ),
Col.WarrantyCalled = Table.AddColumn(Col.IndexSub , "ColWrntTb", each
let
AllDataTable = [FullTable],
WPrv= AllDataTable[Provision Warranty],
WPrd= AllDataTable[PeriodNo],
WCalledinNPrd= AllDataTable[Wrnt called After n Periods],
WInt= Number.IntegerDivide(WCalledinNPrd,1),
WMod = Number.Mod(WCalledinNPrd,Number.Sign(WCalledinNPrd)),
Col.WarrantyCalledValue= Table.AddColumn(AllDataTable, "WarrantyCalledValue", each
if WPrd{[IndexSub]} = 0 or WPrd{[IndexSub]} <= WCalledinNPrd{[IndexSub]} then 0 else
(if WCalledinNPrd{[IndexSub]} <=1 then
1/1* (if Number.IntegerDivide(WCalledinNPrd{[IndexSub]},1)+1= WPrd then
WPrv{[IndexSub]}*(1- WPrd{[IndexSub]}) else
WPrv{[IndexSub]}*(1- WPrd{[IndexSub]})+(try WPrv{[IndexSub]-1} otherwise null)* WPrd{[IndexSub]}) else
2/2*(if WInt{[IndexSub]}+1= WPrd{[IndexSub]} then
(try WPrv{[IndexSub]- WInt} otherwise null )*(1-WMod) else
(try WPrv{[IndexSub]- (WInt+1)} otherwise null )*WMod +
(try WPrv{[IndexSub]- WInt } otherwise null )*(1-WMod))))
in
Col.WarrantyCalledValue),
#"Expanded ColWrntTb" = Table.ExpandTableColumn(Col.WarrantyCalled, "ColWrntTb",
- spinfuzer
AllDataTable[Warranty]{[Index]} or AllDataTable{[Index]}[Warranty] can both work as long as [Index] is a proper reference in the outer table. However you are working with nestedtables so I think you really wanted the nested index so the above does get you want you want in your sceneario.
If Index was indeed an outer index that you wanted to use. You start with a Table, then get the Row and then column. Or start with the Table, get the column and then row. Just be consistent so you don't confuse yourself. I personally did not do it that way because it is slower performance when you refererence an entire table and then drill down into single value. If you plan to do reference a table, then add a step buffer_name = Table.Buffer(AllDataTable) and reference buffer_name[Warranty]{[Index]} instead and you will notice better performance.
if you want to add multiple columns at once, you create a record inside of the AddColumn e.g.
Table.AddColumn(Source, "Custom Col Name", each [ Col1 = "value1", Col2 = "value2"]). Then you can expand the record column and Col1 and Col2 become columns of the table.
- DSR
Concerning adding multiple Columns inside the nested table (not outside), I have tried your method it still give error. I appreciate steps not Function to execute
- spinfuzer
If you are using a function parameter inside of another function parameter (e.g. Table.AddColumn inside of a Table.TransformColumns then you have to replace a second each with (x) =>.
each _ represents the tables in each row in Table.TransformColumns
and (x) => x represents the rows of the table _ in Table.AddColumn
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsmvzC9JVNJRMgRhAyBhoGepFKuDJGMExKbYJIxBkli1mIBksOoBCRoj6/HIz0uB2W8KkTBDkTACOwybDMh+Q2NsMiD7DRGmxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Period = _t, Warranty = _t, #"WarrantyCalled After n periods" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Period", Int64.Type}, {"Warranty", Int64.Type}, {"WarrantyCalled After n periods", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"NestedTable", each _, type table [Category=nullable text, Period=nullable number, Warranty=nullable number, WarrantyCalled After n periods=nullable number]}}), add_index = Table.TransformColumns(#"Grouped Rows", {"NestedTable", each Table.AddIndexColumn(_, "Index")}), add_cols = Table.TransformColumns(add_index, { "NestedTable", each Table.AddColumn(_, "warranty", (x) => [ ColumnA = try _{x[Index]-1}[Warranty] * _{x[Index]-1}[WarrantyCalled After n periods] otherwise null, ColumnB = try _{x[Index]-2}[Warranty] * _{x[Index]-2}[WarrantyCalled After n periods] otherwise null, ColumnC = try _{x[Index]-3}[Warranty] * _{x[Index]-3}[WarrantyCalled After n periods] otherwise null ] ) } ) in add_colsYou can choose to expand the record column after expanding the table, or just add Table.ExpandRecordColumn within the add_col step directly.