Using Excel VBA, and not REgEx, how can I replace text in a Power Query when two characters can change
Hi everyone,
It's me again. Been working on a way to fully automate the reporting for my boss and now I've ran into this problem.
First, here is my m-code (removed most everything after = but left steps). How do I replace every instance of Table_query__23_Table where the 23 is unknown but the new one is known? Reading the m-code, I think it happens 2-3 times, where 1-2 are have "_Table" and the remaining does not.
I've figured out how to change the sources file name, but can't figure how to change the table name.
let Source = Excel.Workbook(File.Contents("PATHtoEXCEL/FILEname.xlsx"), null, true), Table_query__23_Table = Source{[Item="Table_query__23",Kind="Table"]}[Data], #"Changed Type" = REMOVED_VERY_LONG #"Removed Columns" = REMOVED_UNNECESSARY_COLUMNS #"Split Column by Delimiter" = SPLIT_TEAM_LEAD_TO_TWO_COLUMNS #"Changed Type1" = CREATED_HEADERS_FOR_ABOVE #"Removed Columns1" = RREMOVED #"Renamed Columns" = RENAMED #"Sorted Rows" = SORT #"Filtered Rows" = FILTERED_FOR_CURRENT_MONTH #"Sorted Rows1" = SORT (probably a duplicate of the sort aboe) #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows1",{"Agent Name", "QA Score", "Created", "Agent's Team Lead"}) in #"Reordered Columns" I think it's just this line: Tablequery23_Table = Source{[Item="Table_query_23",Kind="Table"]}[Data],
If the first bit required or could it be shortened?
Meaning: TQTable = Source{[Item="Table_query_23",Kind="Table"]}[Data], This way I won't have to change that part.
[link] [comments]
Want to read more?
Check out the full article on the original site