2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

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.

submitted by /u/Difficult_Cricket319
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#Excel alternatives for data analysis
#generative AI for data analysis
#rows.com
#big data management in spreadsheets
#conversational data analysis
#real-time data collaboration
#intelligent data visualization
#no-code spreadsheet solutions
#Excel compatibility
#natural language processing for spreadsheets
#Excel VBA
#Power Query
#table name
#m-code
#replace text
#automate reporting
#data source
#File.Contents
#Table_query
#item