Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jeongkim
Post Prodigy
Post Prodigy

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

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

jeongkim_0-1704159776497.png

 

jeongkim_0-1704162384746.png

 

 

Thank you

4 REPLIES 4
AngeloParana
Resolver I
Resolver I

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

AngeloParana_0-1704170710786.png

 

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
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thank you, 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.