Forum Discussion
Updated Source Name
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"
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