cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Add multiple calculated Columns INSIDE Nested Table without add Custom Column outside, in Power Quer

Question Insert Multiple Columns in Nested Table in Power Query

7 REPLIES 7
Super User

You 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

Table.ExpandRecordColumn(
[
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
,
#"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"

Helper I

Question to

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)),

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",

Super User

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.

Helper I

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

Super User

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]}}),
{
"NestedTable",
each
[
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

You can choose to expand the record column after expanding the table, or just add Table.ExpandRecordColumn within the add_col step directly.

Helper I

Thank you spinfuzer for all your time and advise. I will try the last one.

Another question: can a variable varries where one of its compoment varies?

UnearnedRev.Prv= AllDataTable[Unearned Rev] {[IndexSub]},

Can "UnearnedRev.Prv" varies with "{[IndexSub]}"? or I have to write:

UnearnedRev.Prv= AllDataTable[Unearned Rev]   and

UnearnedRev.Prv{[IndexSub]}

to work

I tried it in the followiong  if gives error: "Expression.Error: The field 'IndexSub' of the record wasn't found."

I do not know the reason, is it from the stucture above, it does not vary or my code mistake?

let

AllDataTable = [ColDoubtfulDebtWrittenOffTb],

UnearnedRev.Prv= AllDataTable[Unearned Rev] {[IndexSub]},

UnearnedRev.Prd = AllDataTable[PeriodNo] {[IndexSub]},

UnearnedRevMaterializedinNPrd = AllDataTable[Unearned Rev Months collected in advance] {[IndexSub]},

UnearnedRevMaterializedinNPrd.Int = Number.IntegerDivide(UnearnedRevMaterializedinNPrd,1),

UnearnedRevMaterializedinNPrd.Mod = Number.Mod(UnearnedRevMaterializedinNPrd,Number.Sign(UnearnedRevMaterializedinNPrd)),

UnearnedRevMaterializedinNPrd.Rounded = Number.RoundAwayFromZero (UnearnedRevMaterializedinNPrd),

if UnearnedRev.Prd = 0 or UnearnedRev.Prd < UnearnedRevMaterializedinNPrd then 0 else

UnearnedRev.Prv*.5)

in

Col.UnearnedRevMaterializedValue),

Super User

If [IndexSub] is coming from your nested table, then no, you can't just do AllDataTable[Unearned Rev] {[IndexSub]}.  Carefully review my examples, any time I am referring to a nested column I am prefixing it with x because my iterator within the Table.AddColumn is   Table.AddColumn(NestedTableColumn, (x) => ......).  If I want to access the nested colum IndexSub I would write x[IndexSub].

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.