•2 min read•from 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
Our take
Are you struggling to automate your reporting in Power Query because of dynamic table names? You're not alone. In Excel VBA, replacing text like "Table_query__23_Table" when the number changes can be tricky, but it's essential for seamless data management. By leveraging VBA, you can streamline your code to make it adaptable. In this guide, we’ll explore how to efficiently update those table names without the need for complex regular expressions, ensuring your automation process remains smooth and effective.
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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Replacing data with power queryi am doing a scrub matching file from data pulled from my company CRM. as you may know most of the information here (company names) are not static. i CANT use fuzzy matching because it makes it worse. so i am trying to make a static list. my list is a 100% accurate i tried myself with xlookup and checking UNIQUES names are all written in the exact same way in the static list, even manually went through some of the examples the problem? while merging the queries tho the names are there the way its on the data and its static version to replace, power query is not detecting some of them. like yeah its working for the 30% of the inputs but the other 70% are appearing as null/blanks. so my theory is correct but why is it not applying to all of them values? english is not my first language so apologies in advance if its hard to understand. i dont want to make this long but i can also further explain step by step how i am doing it. thank you submitted by /u/DifferentMeat9240 [link] [comments]
- Excel Power Query: Renaming Queries Already Associated with Pivot Tables & ChartsHello! I created a massive workbook for a client and parameterized the geography and vintage of my queries in the Power Query Editor so that I can easily make a similar file with different parameters. The problem is I named the queries themselves too specifically (see pic) and now I want to change them but it breaks all of my associated pivot tables and charts. For example, I would like to change ACS_Poverty_County_1YR to ACS_Poverty so that the query names are not misleading when I create an MSA/Zip Code version of the file. This file is way too big to remake so I'm desperate to find a way to fix it without having to remake the visualizations. Any help is much appreciated! https://preview.redd.it/eszqgftt8nsg1.png?width=490&format=png&auto=webp&s=dcaf3b4317b4e955da792bdefbc4ead98ad96f42 submitted by /u/classicjam [link] [comments]
- Having trouble with the logic on how to transfer info from one table to anotherHi Again, First, a screenshot of both tables' headers https://preview.redd.it/cukxw4bobikg1.png?width=615&format=png&auto=webp&s=69c1ceec4141b15ec691b33a4e24466520bb106a The one in black is the source. Agent's Team Lead = Sheet Name in the destination wb. The Orange is the destination, it's also in a separate wb. The problem, there are two tables on each sheet. There are a total of 4 teams. The tables are named T1_In, T1_Ex to T4_In, T4_Ex. How do I know which T? they are on? Once I have that figured out, I need to check if they are already on the table. ...If they are on the table, I have to find out which QA it is (1st, 2nd, 3rd, 4th) based on which is empty ...If not, add them to the table I can't seem to wrap my head around the logic in how to do the above. The source of the sourceTable is a Power Query which pulls from am Excel file. It only has daily updates, meaning only shows new QA from the previous report. These daily reports are not saved, but the wb is. The destination table will then become the source for other Power Queries on that wb's other sheets for daily reporting of the current state of the month. submitted by /u/Difficult_Cricket319 [link] [comments]
- I want to use Power Query to import data received from a client, where the file name changes each month. What's the easiest way to automate this?I used to use VBA for this, but that's a lot more roundabout, and I have a lot less control over the transformation. I have no issues with transforming the actual data itself. My issue lies in the fact that it's a different file each month. Using wildcard formatting, *filehere*.xls* would always pull the correct file. This file is also stored in the same place relative to my spreadsheet each time, but the location of the spreadsheet and folders itself changes each month. In VBA, I could find the relative position quite easily via ThisWorkbook.Path & "\Data\" However, I don't know how to use PQ to import automatically like this, so that I'd always import the correct data simply by refreshing links. I think I've seen people set up a somewhat hacky way, where PQ first reads a table in the workbook to retrieve values, and then uses those to find the file to query. Is that the only way? submitted by /u/space_reserved [link] [comments]