Table.SelecRows from Index >=1 and Index<= Current row position.

[DSR]

Please help me correctly coding Table.SelectRows from 1 to Current Row. Thanks                                           

Three Clustered Tables (subgroups) after Unpivoting then Grouping by)             

Here is the file, that contains the three Sheets and the last query I used, Link from Google Drive, If I made correctly the upload.

https://docs.google.com/spreadsheets/d/1ySatvzBvWTJi-5juSBKukCm06yVFrHHN/edit?usp=drive_link&ouid=11...                        

Columns: {"JobID.EEName", "Case", "CC", "Position", "No of EE Changes", "Yr Move Out", "Yr00", "Yr01", "Yr02", "Yr03", "... "Yr09", "Yr10"},

became Table with the following Column Headers:                                                                               

              Unpivoted became:                      SourceTb            JobID.EEName  Case              CC          Position              No of EE Changes            Yr Move Out      Year              EE Life

Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Case", "CC", "JobID.EEName"}, {{"AllRows Age", each _, type table}}),

              Group by Became:                        Case      CC          JobID.EEName                                                                                                                 

How to write the code to Select Rows from Row [IndexSub] >=1 to the current Row included?                                            

my attached code is selecting all 10 Rows                                                                                                             

Method 1           I used [IndexSub] >=1 and [IndexSub] <=[IndexSub]                      

Method 2:          Index = AllRowsbySub[IndexSub], Index>=1 and Index <= Index                           

Method 3:          (x) variable                                                                                                                                      

None of the methods work                                                                                                                                      

The three Files are in sheets: C.0, C.x and C.n                                                                                                       

--------------------------------

The last method Query I used:

let
Source = Excel.CurrentWorkbook(),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Tb_EE_Longevity_and_Life_C.0" or [Name] = "Tb_EE_Longevity_and_Life_C.x" or [Name] = "Tb_EE_Longevity_and_Life_C.n")),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Name", "Content"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Name", "SourceTb"}}),
#"Expanded Content" = Table.ExpandTableColumn(#"Renamed Columns", "Content", {"JobID.EEName", "Case", "CC", "Position", "No of EE Changes", "Yr Move Out", "Yr00", "Yr01", "Yr02", "Yr03", "Yr04", "Yr05", "Yr06", "Yr07", "Yr08", "Yr09", "Yr10"}, {"JobID.EEName", "Case", "CC", "Position", "No of EE Changes", "Yr Move Out", "Yr00", "Yr01", "Yr02", "Yr03", "Yr04", "Yr05", "Yr06", "Yr07", "Yr08", "Yr09", "Yr10"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Content", each ([No of EE Changes] <> null) and ([No of EE Changes] <> 0)),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"SourceTb", "JobID.EEName", "Case", "CC", "Position", "No of EE Changes", "Yr Move Out"}, "Year", "EE Life"),

#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Case", "CC", "JobID.EEName"}, {{"AllRows Age", each _, type table}}),
AddIndexSub = Table.AddColumn(#"Grouped Rows", "ColIndexSub", each Table.AddIndexColumn([AllRows Age], "IndexSub", 0,1)),

FilteredRows = Table.AddColumn(AddIndexSub, "Custom", each let
AllRowsbySub = [ColIndexSub],
Year = AllRowsbySub[Attribute],
Index = AllRowsbySub[IndexSub],
Longevity =AllRowsbySub[Value],
EEStart=Table.AddColumn(AllRowsbySub, "FilteredRow1", each Table.SelectRows(AllRowsbySub, (row) => row[IndexSub] >= 1 and row[IndexSub] <= Table.PositionOf(AllRowsbySub, row) + 1))
in
EEStart),
#"Removed Other Columns" = Table.SelectColumns(FilteredRows,{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"SourceTb", "JobID.EEName", "Case", "CC", "Position", "No of EE Changes", "Yr Move Out", "Year", "EE Life", "IndexSub", "FilteredRow1"}, {"SourceTb", "JobID.EEName", "Case", "CC", "Position", "No of EE Changes", "Yr Move Out", "Year", "EE Life", "IndexSub", "FilteredRow1"})
in
#"Expanded Custom"

[DSR · Accepted solution]

I think I solve it by replace the last  "row" word (Var) by "_". but the 11 rows is ging same answer as the 10th row

EEStart=Table.AddColumn(AllRowsbySub, "FilteredRow1", each Table.SelectRows(AllRowsbySub, (row) => row[IndexSub] >= 1 and row[IndexSub] <= Table.PositionOf(AllRowsbySub, row) + 1))
in
EEStart),

The correction:

EEStart=Table.AddColumn(AllRowsbySub, "FilteredRow1", each Table.SelectRows(AllRowsbySub, (row) => row[IndexSub] >= 1 and row[IndexSub] <= Table.PositionOf(AllRowsbySub, _) + 1))
in
EEStart),

[v-sgandrathi]

Hello @DSR,

Thanks for using Microsoft Fabric Community Forum.

To upload the sample Data here please go through the link below:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community.

Kindly do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Thankyou.

[DSR]

let
Source = Excel.CurrentWorkbook(),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Tb_EE_Longevity_and_Life_C.0" or [Name] = "Tb_EE_Longevity_and_Life_C.x" or [Name] = "Tb_EE_Longevity_and_Life_C.n")),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Name", "Content"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Name", "SourceTb"}}),
#"Expanded Content" = Table.ExpandTableColumn(#"Renamed Columns", "Content", {"JobID.EEName", "Case", "CC", "Position", "No of EE Changes", "Yr Move Out", "Yr00", "Yr01", "Yr02", "Yr03", "Yr04", "Yr05", "Yr06", "Yr07", "Yr08", "Yr09", "Yr10"}, {"JobID.EEName", "Case", "CC", "Position", "No of EE Changes", "Yr Move Out", "Yr00", "Yr01", "Yr02", "Yr03", "Yr04", "Yr05", "Yr06", "Yr07", "Yr08", "Yr09", "Yr10"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Content", each ([No of EE Changes] <> null) and ([No of EE Changes] <> 0)),

#"Merged Queries" = Table.NestedJoin(#"Filtered Rows1",{"Case", "CC"},Q_CaseCC,{"Case", "CC"},"Q_CaseCC",JoinKind.LeftOuter),
#"Expanded Q_CaseCC" = Table.ExpandTableColumn(#"Merged Queries", "Q_CaseCC", {"CaseNo", "CC.Code"}, {"CaseNo", "CC.Code"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Expanded Q_CaseCC",{"SourceTb", "JobID.EEName", "Case", "CC", "Position", "CaseNo", "CC.Code", "No of EE Changes", "Yr Move Out", "Yr00", "Yr01", "Yr02", "Yr03", "Yr04", "Yr05", "Yr06", "Yr07", "Yr08", "Yr09", "Yr10"}),

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns1", {"SourceTb", "JobID.EEName", "Case", "CC", "Position", "CaseNo", "CC.Code", "No of EE Changes", "Yr Move Out"}, "Year", "EE Age"),

#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Case.CC.JobID.EEName SortOrder", each Text.From([CaseNo])&"."&[CC.Code]&"."&[JobID.EEName]),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Case.CC.JobID.EEName SortOrder", Order.Ascending}, {"Year", Order.Ascending}}),

#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Case", "CC", "JobID.EEName","Case.CC.JobID.EEName SortOrder"}, {{"AllRows Age", each _, type table}}),
#"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Case.CC.JobID.EEName SortOrder", Order.Ascending}}),

// Add an Index column to the grouped rows table (from 0)
AddIndexSub = Table.TransformColumns(#"Sorted Rows1", {{"AllRows Age", each Table.AddIndexColumn(_, "IndexSub", 0, 1), Int64.Type}}),

SelectRows = Table.AddColumn(AddIndexSub, "Custom", each let
AllRowsbySub = [AllRows Age],
Year = AllRowsbySub[Year],
Index = AllRowsbySub[IndexSub],
Longevity =AllRowsbySub[EE Age],
EEStart=Table.AddColumn(AllRowsbySub, "FilteredRow1", each Table.SelectRows(AllRowsbySub, (row) => row[IndexSub] >= 1 and row[IndexSub] <= Table.PositionOf(AllRowsbySub, _)+1))
in
EEStart),
#"Removed Other Columns" = Table.SelectColumns(SelectRows,{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"FilteredRow1"}, {"FilteredRow1"}),
#"Expanded FilteredRow1" = Table.ExpandTableColumn(#"Expanded Custom", "FilteredRow1", {"SourceTb", "JobID.EEName", "Case", "CC", "Position", "No of EE Changes", "Yr Move Out", "Year", "EE Age", "Case.CC.JobID.EEName SortOrder", "IndexSub"}, {"SourceTb", "JobID.EEName", "Case", "CC", "Position", "No of EE Changes", "Yr Move Out", "Year", "EE Age", "Case.CC.JobID.EEName SortOrder", "IndexSub"}),
#"Sorted Rows2" = Table.Sort(#"Expanded FilteredRow1",{{"Case.CC.JobID.EEName SortOrder", Order.Ascending}, {"Year", Order.Ascending}})
in
#"Sorted Rows2"

[DSR]

Thank Administrator. Still I can not see link to upload the excel files that contains the three Tables the source Query, which containt one of the codes I used in Table.SelectRows