Extracting Values From a List & Inserting in Table at Specific Rows

Hi Jimmy,

First, thanks very much for helping! Super useful!

I have a couple of questions where I don't quite understand how the code is working due to my limited knowledge:

1) For the Base_AnalysisTimeline table, why did you use the Table/JSON, etc. method to create the table rather than the usual "Source = Currentworkbook() etc."? Is there an advantage of doing it this way?

2) For "BenefitGUID = #table({"Benefit GUID"}, {{"U1B1"}, {"U1B2"}, {"U1B3"}})", I guess I could replace this with a query which calls List.Distinct from my existing Benefits_Calc table and remove the other columns to create a table from that? The number of GUIDs will vary over time, so I will need the table to update row numbers dynamically.

Thanks again!

Hi Jimmy801 ,

Here is what I ended up with (note that I added a couple of columns on the right for additional values too):

let
    //Setup Base Table with GUIDs and Count
    Table_Setup1 =
        let
        //Get the source table and update the types
        Base_AnalysisTimeline =
        let
            Source = Excel.CurrentWorkbook(){[Name="Base_AnalysisTimeline"]}[content],
            #"Changed Type" = Table.TransformColumnTypes(Base_AnalysisTimeline,{{"Count", Int64.Type}})
        in
            #"Changed Type",
        
        
        //Create a table of the GUIDs
        BenefitGUID = Table.FromList(
            List.Distinct(Benefits_Calc[Benefit GUID])
        ),

        //Add a column containing a table of the count for every BenefitGUID
        Multiply = Table.AddColumn
        (
            BenefitGUID,
            "BaseAnalysisTimeLine",
            each Base_AnalysisTimeline
        ),

        //Expand the Tables in the New Column
        expandedTable = Table.ExpandTableColumn(Multiply, "BaseAnalysisTimeLine", {"Count"}, {"Count"}),
        //Rename GUID Column
        #"Renamed Columns" = Table.RenameColumns(expandedTable,{{"Column1", "Benefit GUID"}})
        
        in
            #"Renamed Columns",

    //Add Inflation Column to Table_Setup1
    Table_Setup2 = 
        let   
        //Get number of unique Benefit GUIDs
        noUniqueGUID = List.Count(List.Distinct(Benefits_Calc[Benefit GUID])),
        //Create temporary list of Inflation Rates
        tempList = Base_AnalysisTimeline[Compound Inflation Rate from Base],
        //Repeat the rows the same as unique GUIDs
        repeatList = List.Repeat(tempList, noUniqueGUID),
        tempTable = Table.FromColumns({repeatList}),
        //add index to that table:
        tempTableWithIndex = Table.AddIndexColumn(tempTable, "joinIndex", 0, 1),
        //add index to passed in table:
        targetTableWithIndex = Table.AddIndexColumn(Table_Setup1, "joinIndex", 0, 1),
        joinedTable = Table.Join(targetTableWithIndex , "joinIndex", tempTableWithIndex , "joinIndex", JoinSide.Left),
        //remove the index column from joined table we dont need it
        joinedTableWithoutIndex = Table.RemoveColumns(joinedTable, "joinIndex"),
        #"Renamed Columns" = Table.RenameColumns(joinedTableWithoutIndex,{{"Column1", "Compound Inflation Rate from Base"}})
        in
            #"Renamed Columns",

    //Add Discount Factor Column to Table_Setup2
    Table_Setup3 = 
        let   
        //Get number of unique Benefit GUIDs
        noUniqueGUID = List.Count(List.Distinct(Benefits_Calc[Benefit GUID])),
        //Create temporary list of Inflation Rates
        tempList = Base_AnalysisTimeline[Discount Factor],
        //Repeat the rows the same as unique GUIDs
        repeatList = List.Repeat(tempList, noUniqueGUID),
        tempTable = Table.FromColumns({repeatList}),
        //add index to that table:
        tempTableWithIndex = Table.AddIndexColumn(tempTable, "joinIndex", 0, 1),
        //add index to passed in table:
        targetTableWithIndex = Table.AddIndexColumn(Table_Setup2, "joinIndex", 0, 1),
        joinedTable = Table.Join(targetTableWithIndex , "joinIndex", tempTableWithIndex , "joinIndex", JoinSide.Left),
        //remove the index column from joined table we dont need it
        joinedTableWithoutIndex = Table.RemoveColumns(joinedTable, "joinIndex"),
        #"Renamed Columns" = Table.RenameColumns(joinedTableWithoutIndex,{{"Column1", "Discount Factor"}})
        in
            #"Renamed Columns"


in
    Table_Setup3

Not the most elegant solution I'm sure, but I'm happy with it for my first attempt.

Thanks again for all the help!