Forum Discussion

nataliesmiy1357's avatar
nataliesmiy1357
Helper IV
2 years ago

Updated Source Name

Good morning - I have a report that is connected to a SharePoint folder.   In my OR Report table, The original file was named ""2016-2023 OR data. PowerBI format.xlsx" but it has now been changed t...
Need Help
Show and Tell
Tutorial Requests
christinepayton's avatar
christinepayton
Most Valuable Professional
2 years ago

Yes, you can just open up the advanced query editor in Power Query and change the filename that the filter is using there. It's probably not referenced more than once, but if it were you could just replace it in each place it's used. 

nataliesmiy1357's avatar
nataliesmiy1357
Helper IV
2 years ago

That didn't seem to work - I got a bunch of other errors.... 

 

 

Here is the advanced query editor:

let
Source = SharePoint.Files("https://myteams.toyota.com/sites/tmnanavoPC/PY", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "PY Dashboard")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Name] = "OR data. PowerBI format.xlsx")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded Table Column1", "Merged", each Text.Combine({[Plant], [Line]}, " "), type text),
#"Replaced Value" = Table.ReplaceValue(#"Inserted Merged Column","TMMK Line 1","TMMK-1",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","TMMK Line 2","TMMK-2",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","TMMK Line 3","TMMK-3",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","TMMC North","TMMC-N",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","TMMC South","TMMC-S",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","TMMC West","TMMC-W",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","TMMAL 2GR Kai","TMMAL GR-Kai V6",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","TMMAL TNGA #1","TMMAL TNGA 1 L4",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","TMMAL TNGA #2","TMMAL TNGA 2 L4",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","TMMI East","TMMI-E",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","TMMI West","TMMI-W",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","TMMWV DAT 3","TMMWV DAT High #3",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","TMMWV DAT 4","TMMWV DAT High #4",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","TMMWV DAT 1","TMMWV DAT Mid #1",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","TMMWV DAT 2","TMMWV DAT Mid#2",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","TMMWV 2GR Kai","TMMWV GR-Kai V6",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15","TMMK PWT 2GR Kai","TMMK PWT GR-Kai V6",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","TMMK PWT A25 #1","TMMK PWT TNGA L4 #1",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","TMMK PWT A25 #2","TMMK PWT TNGA L4 #2",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18","TMMTX Tundra#(lf)Tacoma","TMMTX Tundra/Tacoma",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","TMMWV 2ZR","TMMWV ZR-Kai L4",Replacer.ReplaceText,{"Merged"}),
#"Inserted Merged Column1" = Table.AddColumn(#"Replaced Value20", "Date and Plant", each Text.Combine({Text.From([DATE], "en-US"), [Merged]}, " "), type text),
#"Replaced Value21" = Table.ReplaceValue(#"Inserted Merged Column1","TMMWV HV#(lf)Transaxle #1","TMMWV HV Tran #1",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value22" = Table.ReplaceValue(#"Replaced Value21","TMMWV HV#(lf)Transaxle #2","TMMWV HV Tran #2",Replacer.ReplaceText,{"Merged"})
in
#"Replaced Value22"

 

  • christinepayton's avatar
    christinepayton
    Most Valuable Professional
    2 years ago

    Oh, this looks like the SP Folder connector. Do you actually have multiple files you're connecting to, or just one? The folder connector has a bunch of baggage, there's another reference in one of the objects in the folder it auto-creates that you will also want to update. I did an example changing the path for the folder connector here, this might help: https://www.youtube.com/watch?v=5SFxHffE2TA