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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
jeongkim
Helper V
Helper V

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
Frequent Visitor

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

Thank you, 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.