•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Replacing data with power query
Our take
When working with Power Query for data scrubbing, particularly when matching company names from your CRM, achieving accuracy can be challenging. If you've created a static list and ensured that all names match exactly, yet still encounter issues where 70% of the inputs appear as null, it’s essential to investigate further. Common pitfalls might include hidden characters or formatting discrepancies. Consider examining your static list and the CRM data for any inconsistencies that could hinder the merge process.
i 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
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Power query and manual table next to itHi, I want to pull data verbatim from a spreadsheet my team uses and use data from it for my own purposes. The main goal for using power query is that the data updates on my spreadsheet. Mainly, if any new entries are added at the bottom. I also have some manual fields that I need to add that correspond with the power query data. I've added another table beside the power query data, and filtering it causes the data on both sides to adjust correctly. I'm mainly concerned that, if the entries are rearranged or sorted on the original sheet, that my tables will not align after a refresh. Also, if a refresh would break my table alignments at any point. Is my fear founded? Is there a way to combine the two features that I need into a single table? submitted by /u/Perspective-Guilty [link] [comments]
- Issue with Excel Power QueryIn my excel workbook I have a long string of queries to get the results I want, however I am noticing a small number of duplicates that SHOULDNT be able to exist. In my first query in this string, I am adding a new column (SelectionBucket), based on two other columns - Works. Then taking this SelectionBucket column, and adding another column (IsRequiredBucket) based on [SelectionBucket] returning one of the required values - Works. I then am adding an index at this time (CourseIndex) - Works. Result: Courses have Index, and SelectionBucket and IsRequiredBucket as options. Q2 (Reference to Q1): Adding Column (IsRequiredCandidate) where [IsRequiredBucket] = True - Works. Filters out to ONLY true values next, and sorted on (Name) (Ascending), (SelectionBucket) (Ascending), (EMark) (Decending) - Works. Result: Filtering down to only RequiredBuckets, sorted by Best to Worst. Q3 (Reference Q2): I group the rows based on (Name) and (SelectionBucket), call it [AllRows]. Add Column (TopRequired) with Table.FirstN(Table.Sort([AllRows], {{"EMark",Order.Descending}}),1) to return the BEST value - Works. Expand the [TopRequired] Table, excluding Name and SelectionBucket - Works. Add column (SelectionType) = "Required" This is where I am not sure if it is working or not, because for 99% of my data, this works. But for some of the entries, this isn't working. Add one more column (IsRequiredSelected) to check (SelectionType), if "Required" = TRUE. Result should be: Selection of one result for each of the buckets available per entry, and setting its (IsRequiredSelected) value to TRUE. Q4 (Reference Q1): I merge Q4(which is just Q1), to Q3, matching on (CourseIndex), Expand the merge (SelectionType). Rename (SelectionType) to (RequiredTag). Add column (IsRequiredSelected) checking [RequiredTag] to return TRUE for "Required", FALSE otherwise. Q5 (Reference Q4): Add Column (IsAdditionalCandidate) checking [IsRequiredSelected] = FALSE. Filter (IsAdditionalCandidate) for only TRUE values. Sort by (Name) (Ascending), (EMark) (Descending). Result: Check if (IsRequiredSelected) = TRUE and clearing them out. Q6 (Reference Q5): Group by Name -> [AllRows] with operation of All Rows. Add column (TopAdditional) coded =Table.FirstN(Table.Sort([AllRows], {{"EMark",Order.Descending}}),7). Expand the table [TopAdditional] excluding (Name). Add column (SelectionType) = "Additional" Result: Taking only records that are marked as "Additional" and taking the best 7 results for each (Name). Q7 is an appended query of combining Q3 and Q6, where it should take the Q3 Results, and adds the Q6 results to it, which should result in NO duplicates. Issue: I am receiving some of my entries as a duplicate through a query check, where I see the one record as both an Additional and as a Required. I am not sure WHY or where it is broken, other than where I think it is... I realize I could have done this in less Queries, however I wanted to verify along each step of the way if something went wrong, so that I could fix it as a portion, instead of having to delete and re-write everything. Please note that I CANNOT share the excel file data itself, as it contains confidential information within it. If I haven't explained a step clearly enough, please let me know and I will try to add further information on it. submitted by /u/DLCamilla [link] [comments]
- PowerQuery and add manual dataHi everyone, I have a Power Query in Excel that outputs a table with [title] [date]. I need to manually add the Sprint number in an extra column [sprint] to specific combinations, aka "I will work on this this month." The problem is every time the query refreshes, any manually entered data gets lost or misaligned. - New rows come with the needed values. - Row order changes. Because this table is used by many people, I want them only to add the sprint number, nothing else, no copying data or anything. I would like to know more about your experiences when data needs to be written infrequently but many times. I am open to know more for powerbi options direct dashboard too. submitted by /u/No_Solid2349 [link] [comments]
- Power Query Merge causing missing values (~7k difference in totals)I’m merging two large tables in Power Query, but I’m running into a data accuracy issue. After the merge, I noticed that the sum of a specific column is about 7,000 less than the total in the original table. So it looks like some data is missing during the merge. I’m working with large datasets, and I want to properly debug this. What are the most common reasons for missing values after a merge? Could this be join (e.g., Inner vs Left)? How can I identify exactly which rows are missing? What’s the best way to troubleshoot this and make sure no data is lost in the merge? Any help or tips would be appreciated 🙏 submitted by /u/Resident_Quantity827 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#natural language processing for spreadsheets#rows.com#financial modeling with spreadsheets#Excel compatibility#natural language processing#Excel alternatives#power query#data scrub#static list