Excel PowerQuery

[ElRugg]

I have a list of items and categories in a worksheet in Excel on Sheet 1. I get 2 different monthly reports and use Power Query to adjust and combine each new month into Sheets 2 and 3 (1 for each report). I use the merged queries option to "vlookup" the items from the report and match the categories.

I have 1 item where for whatever reason the category will not transfer over. Every other item correctly and even does this item correctly on the Sheet2 query, just not on Sheet3. I have tried copying the item directly from the invoice into the Sheet 1 list. I have added additional items at the bottom and it updates correctly so not Sheet1 is limited for what rows it's considering. I've already done Query steps to trim and case match the text.

I'm fresh out of ideas at this point and would appreciate either suggestions, or workarounds. I've thought about doing a macro like if Column A = "this item" then Column B = "Category" but there's gotta be an easier way?

 

[JGordon11]

Check the cells actually equal one another (example = Sheet1!A1 = Sheet2!B1). If it returns false then check the length of value in the cells that should match one another. One may have one or more invisible extra characters. If the LEN()s are the same then check the ascii code of each character using CODE(MID()) and make sure each character matches (character 1 through LEN()). If the lengths are the same and each character is the same then the =A1 = B1 check should have returned TRUE.

 

[ElRugg]

Check the cells actually equal one another (example = Sheet1!A1 = Sheet2!B1). If it returns false then check the length of value in the cells that should match one another. One may have one or more invisible extra characters. If the LEN()s are the same then check the ascii code of each character using CODE(MID()) and make sure each character matches (character 1 through LEN()). If the lengths are the same and each character is the same then the =A1 = B1 check should have returned TRUE.

The test does come back true.

 

[Alex Blakenburg]

Just curious did you do the character by character "code" match ?

 

[ElRugg]

Just curious did you do the character by character "code" match ?

Yes, the len and every character code matches exactly.

 

[ElRugg]

I got it!

Within the Power Query steps, I trimmed the column before the merge but didn't have the change text (all uppercase) until after. When I copied from the power query result table to the groups, it was after that uppercase function happened and was a match but the raw data wouldn't have had that yet. Quick reorder of the functions and looks good to go now.

Thanks for letting me talk this out.

 

[Alex Blakenburg]

No problem. Thank you for letting us know how you figured it out.