Get the data from excel tab orders not from dynamic tab names

[jeongkim]

Hi,

I would like to get the data from Tab#1 Tab#2 such with this order not with tab name as the names are dynamic as per date. 

e.g. 2023 will be changing to 2024,,,2025 

Q3 will be changing to Q4,,,Q1,,,Q2

So hope to get the data with just order like first tab, second tab,,,no matter what the name is. 

FYI, i'm getting data through Sharepoint folder

Thank you

[AngeloParana]

Use this code, the number inside the brace indicate the index of table you want to get. 

[jeongkim]

Can you re-write the full code pls?

let
Source = SharePoint.Contents("https://nokia.sharepoint.com/sites/korea-pmo-pbi-data-repository", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"KR PO Plan" = #"Shared Documents"{[Name="KR PO Plan"]}[Content],
SKT = #"KR PO Plan"{[Name="SKT"]}[Content],
#"SK_5G Bottom up plan" = SKT{[Name="CT SK_5G Bottom up plan"]}[Content],
#"Filtered Hidden Files1" = Table.SelectRows(#"SK_5G Bottom up plan", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type any}, {"Column14", type text}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type text}, {"Column28", type text}, {"Column29", type any}, {"Column30", type any}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",20),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"QTY", "2023_1Q_QTY"}, {"Revenue", "2023_1Q_OI"}, {"QTY_1", "2023_2Q_QTY"}, {"Revenue_2", "2023_2Q_OI"}, {"QTY_3", "2023_3Q_QTY"}, {"Revenue_4", "2023_3Q_OI"}, {"QTY_5", "2023_4Q_QTY"}, {"Revenue_6", "2023_4Q_OI"}, {"QTY_7", "2023_FY_QTY"}, {"Revenue_8", "2023_FY_OI"}, {"CT SK_5G Bottom up plan_20231017 update_v1.0.xlsx", "Update Version"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column13", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"2023_FY_OI", Int64.Type}, {"2023_FY_QTY", Int64.Type}, {"2023_4Q_OI", Int64.Type}, {"2023_4Q_QTY", Int64.Type}, {"2023_3Q_OI", Int64.Type}, {"2023_3Q_QTY", Int64.Type}, {"2023_2Q_OI", Int64.Type}, {"2023_2Q_QTY", Int64.Type}, {"2023_1Q_OI", Int64.Type}, {"2023_1Q_QTY", Int64.Type}, {"Unit Price(KRW)", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 1, 1, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Update Version", "Project", "Product", "Product Detail", "Category", "Item", "LE", "Material Code (BoQ level)", "Trim Material", "GIC", "Item Detail", "Unit Price(KRW)", "Chk", "Index"}, "Attribute", "Value")
in
#"Unpivoted Columns"

[amitchandak]

@jeongkim , You use sheet by index or can create dynamic code based on search. I have done it for file name and sheet name

#"Navigation 4" = #"Navigation 3"{[Name = "Data"]}[Content],
_name = List.Max(List.Select(#"Navigation 4"[Name],each Text.Contains(_, "Filenamehaving"))),
#"Navigation 5" = #"Navigation 4"{[Name = _name]}[Content],
#"Imported Excel workbook" = Excel.Workbook(#"Navigation 5", null, true),
_sheet = List.Max(List.Select(#"Imported Excel workbook"[Name],each Text.Contains(_, "data"))),
#"Filtered rows" = Table.SelectRows(#"Imported Excel workbook", each [Name] = _sheet),

[jeongkim]

Thank you, 

Where should I paste that code? sitll a bit confused,,