Forum Discussion

DSR's avatar
DSR
Icon for Resolver I rankResolver I
2 years ago

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

Need Help

7 Replies

  • spinfuzer's avatar
    spinfuzer
    Icon for Solution Sage rankSolution Sage
    2 years ago

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

     

    • DSR's avatar
      DSR
      Icon for Resolver I rankResolver I
      2 years ago

       

      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's avatar
        spinfuzer
        Icon for Solution Sage rankSolution Sage
        2 years ago

        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.