Forum Discussion
Helper IVChange sheet name from the source
Hi Guys,
My sheet name changes from the plug in Feature Cycle Time to Feature Cycle Time UAT , I try to change the code, but no success.
Anyone can help me I appreciate, tks
let
Source = SharePoint.Files(https://......., [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path] , DataSource)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], "Feature_Cycle_Time_Q")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (4)", each #"Transform File (5)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (5)"(#"Sample File (5)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Projet", type text}, {"Clé", type text}, {"Résumé", type text}, {"État", type text}, {"Création", type date}, {"Attente", type text}, {"Début", type date}, {"Fin", type date}, {"Cycle Time Epics (j)", Int64.Type}, {"80c CT Epic", Int64.Type}}),
Custom1 = Table.AddColumn(#"Changed Type", "CODE_JIRA", each Text.BeforeDelimiter([Clé], "-"), type text),
#"Added Conditional Column" = Table.AddColumn(Custom1, "Type", each if [80c CT Epic] = null then "Story" else "Feature"),
#"Personalizado Adicionado" = Table.AddColumn(#"Added Conditional Column", "Feature_Name", each if [80c CT Epic] <> null then [Résumé] else null),
#"Preenchido para Baixo" = Table.FillDown(#"Personalizado Adicionado",{"Feature_Name"}),
#"Removed Top Rows" = Table.Skip(#"Preenchido para Baixo",1),
#"Coluna Condicional Adicionada" = Table.AddColumn(#"Removed Top Rows", "Ref_period", each if Text.Contains([Source.Name], "Q1_2025") then #date(2025, 3, 31) else if Text.Contains([Source.Name], "Q2_2025") then #date(2025, 6, 30) else if Text.Contains([Source.Name], "Q3_2025") then #date(2025, 9, 30) else if Text.Contains([Source.Name], "Q4_2025") then #date(2025, 12, 31) else if Text.Contains([Source.Name], "Q1_2026") then #date(2026, 3, 31) else if Text.Contains([Source.Name], "Q2_2026") then #date(2026, 6, 30) else if Text.Contains([Source.Name], "Q3_2026") then #date(2026, 9, 30) else if Text.Contains([Source.Name], "Q4_2026") then #date(2026, 12, 31) else if Text.Contains([Source.Name], "Q1_2027") then #date(2027, 3, 31) else if Text.Contains([Source.Name], "Q2_2027") then #date(2027, 6, 30) else if Text.Contains([Source.Name], "Q3_2027") then #date(2027, 9, 30) else if Text.Contains([Source.Name], "Q4_2027") then #date(2027, 12, 31) else null),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Coluna Condicional Adicionada",{{"Ref_period", type date}})
in
#"Tipo Alterado"
The issue is likely not in the main query. Your query calls the generated Transform File function:
#"Transform File (5)"
That function usually contains the Excel sheet/table selection, so changing the sheet name in the main query won't work.
In Power Query:
Go to Transform Sample File (5) / Transform File (5).
Look for a step similar to:
Source{[Item="Feature Cycle Time",Kind="Sheet"]}[Data]
Change it to:
Source{[Item="Feature Cycle Time UAT",Kind="Sheet"]}[Data]
Also check the Sample File (5) query and make sure it points to a file containing the new sheet name.
Refresh the main query.
If the existing code is something like:
Source{[Item="Feature Cycle Time",Kind="Sheet"]}[Data]
then the direct replacement is simply:
Source{[Item="Feature Cycle Time UAT",Kind="Sheet"]}[Data]
Important: Don't change Source.Name or the "Feature_Cycle_Time_Q" filter in your posted query unless the Excel file name itself has also changed. The sheet name is handled inside the Transform File/Sample File function.
3 Replies
- DaniyalKhaleel1
Resolver I
The issue is likely not in the main query. Your query calls the generated Transform File function:
#"Transform File (5)"
That function usually contains the Excel sheet/table selection, so changing the sheet name in the main query won't work.
In Power Query:
Go to Transform Sample File (5) / Transform File (5).
Look for a step similar to:
Source{[Item="Feature Cycle Time",Kind="Sheet"]}[Data]
Change it to:
Source{[Item="Feature Cycle Time UAT",Kind="Sheet"]}[Data]
Also check the Sample File (5) query and make sure it points to a file containing the new sheet name.
Refresh the main query.
If the existing code is something like:
Source{[Item="Feature Cycle Time",Kind="Sheet"]}[Data]
then the direct replacement is simply:
Source{[Item="Feature Cycle Time UAT",Kind="Sheet"]}[Data]
Important: Don't change Source.Name or the "Feature_Cycle_Time_Q" filter in your posted query unless the Excel file name itself has also changed. The sheet name is handled inside the Transform File/Sample File function.
fbittencourtTks for the reply I found a code to replace the eveytime the name cahnges formthe plug in UAT Prod
(Parameter4 as binary) => let
Source = Excel.Workbook(Parameter4, null, true),
#"Feature Cycle Time Cardif1" = Table.SelectRows(Source,each Text.Contains([Name],"Feature Cycle Time")){0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Feature Cycle Time Cardif1", [PromoteAllScalars=true])
in
#"Promoted Headers"
- ShahRukhSameer
Impactful Individual
Hi fbittencourt,
I don't think the issue is in the main query itself. Your main query is calling the custom function:
#"Transform File (5)"([Content])
So I would check the "Transform File (5)" function first. The sheet name is probably being referenced there, and possibly in "Sample File (5)" as well.
If the sheet was renamed from:
Feature Cycle Time
to:
Feature Cycle Time UAT
then look inside "Transform File (5)" for something like:
Source{[Item="Feature Cycle Time",Kind="Sheet"]}[Data]
and change it to:
Source{[Item="Feature Cycle Time UAT",Kind="Sheet"]}[Data]
You might also see it being filtered instead, for example:
Table.SelectRows(Source, each [Item] = "Feature Cycle Time")
In that case, just change the sheet name to:
Table.SelectRows(Source, each [Item] = "Feature Cycle Time UAT")
I would also check "Sample File (5)". Your main query has:
Table.ColumnNames(#"Transform File (5)"(#"Sample File (5)"))
so the sample file needs to contain the new "Feature Cycle Time UAT" sheet as well.
The flow is basically:
Main Query
→ Transform File (5)
→ Sample File (5)
→ Excel.Workbook(...)
→ Feature Cycle TimeThe other step you mentioned:
#"Filtered Rows1" =
Table.SelectRows(#"Filtered Rows",
each Text.Contains([Name], "Feature_Cycle_Time_Q"))is filtering the file name, not the Excel sheet name. So I wouldn't change that unless the actual file name has also changed.
I would start with "Transform File (5)" and check how the sheet is being selected. If it's still looking for "Feature Cycle Time", changing that to "Feature Cycle Time UAT" should resolve the issue.
