Forum Discussion
How to match and assign a word to a letter in a cell
Eqa33 You missed the most important part and that is that you need to extract the one Letter from the code first. You are now trying to merge the full product code in the Sales table with the category code in the Category table. Obviously, that's not going to return any match.
I've added the step that extracts the code and changed the merge query. It works now.
OliverScheurich Ok, now I understand. But I have a problem when I merge the query I cannot I get a recognition of the letter buried in the middle of the product number it just produces a column of null and as consequence the category name is all null as well. What am I doing incorrectly? Thank you
Eqa33 Added a few steps to your query. These extract the letter codes and merge them with the ones in the Code table. See if this does what you need.
- Ok, I see know. I never would have thought to or known how to, enter that formula to seperate the Code Letters from the Product numbers. Thanks very much. I'm teaching myself Power Query. it's very powerful but also quite complex. Thanks for your help once again. Fantastic.
Eqa33 The last step of the 2022 Sales SOH query creates a custom column that takes the text from Column1 and removes any dot "." or number from "0" to "9", thus leaving only the single letter.
Now, in the Source step of the Merge1 query you select Custom and Code as the matching columns.
- Thanks very much it works well now. I'm still not sure what step I missed. Could you explain it to me in simple language. Thank you once again
Eqa33 You missed the most important part and that is that you need to extract the one Letter from the code first. You are now trying to merge the full product code in the Sales table with the category code in the Category table. Obviously, that's not going to return any match.
I've added the step that extracts the code and changed the merge query. It works now.
Riny_van_Eekelen Tell me if this is what you want to see ,please.
Eqa33 Upload the file please. I need to see the Merge step. What you are merging and how. Did you extract the letter code in any of the other queries, by the way?
Riny_van_Eekelen Not sure if this helps
Eqa33 Can't tell until you show me what you are doing.
- Thanks for your answer. I am obviously doing something wrong. When I merge the 2 tables as you have shown I get null in every row. What am I doing incoirrectly?
