Correct Imported Names from Power Query

kohltonyork

New Member
Joined
Dec 29, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Hello, I have recently been working on a spreadsheet to analyze NBA stats and other data. I have pretty much used Power Query to import all data from numerous different sites. For the most part it has worked great, I use a VLOOKUP function to import the data into one master sheet to see all of the most important information in a central location.

My problem is when importing the data from numerous sites, player names can very site to site. A player like “Michael Porter Jr.” has this problem as some sites he is listed only as “Michael Porter”. My initial idea worked for this case as I used power query so split the names at each space delimiter and removed the 3rd column which was always the suffix after the name. But another problem arose with a player named “C.J. McCollum” where on some sites he’s listed as “CJ” a subtle difference that completely messes up the VLOOKUP function. Any ideas on how I can fix this?

Thank you much!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

kohltonyork

New Member
Joined
Dec 29, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
the question is about vlookup or power query?
VLOOKUP. I just wanted to make it clear that the information was being imported through power query, I wasn’t sure if that had an effect on being able to change it.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
If VLOOKUP you need to wait for any Master of Formula

for Power Query the best way is a post a link to the shared excel file with your source data and expected result
(IMHO, mixing formulas and Power Query is not the right practice)
 
Last edited:

kohltonyork

New Member
Joined
Dec 29, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

If VLOOKUP you need to wait for any Master of Formula

for Power Query the best way is a post a link to the shared excel file with your source data and expected result
(IMHO, mixing formulas and Power Query is not the right practice)

As you said I would like to get away from using VLOOKUP if at all possible. It’s complex and requires very specific information.

Let me explain this workbook a little more, I’m inexperienced in Excel so I’m sure this is very very inefficient.

For starters, Mastersheet is my main sheet where I want all my key information located. The player names and salary are from players that are playing games that night. The information is pulled using VLOOKUP from FanDuel sheet. The FanDuel sheet’s source is another excel workbook where I copy and paste the nightly CSV page.

All other sheets in the workbook are their own individual power query from a separate website.

On the Mastersheet, you will notice cell I23 is blank. (There are more blanks of players not on the stat sheet due to injury.) On the NumberFire sheet you will see CJ McCollum is projected 35.68 minutes but VLOOKUP doesn’t see this because on the Mastersheet it’s listed as CJ as opposed to C.J. on NumberFire.
 

kohltonyork

New Member
Joined
Dec 29, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows

As you said I would like to get away from using VLOOKUP if at all possible. It’s complex and requires very specific information.

Let me explain this workbook a little more, I’m inexperienced in Excel so I’m sure this is very very inefficient.

For starters, Mastersheet is my main sheet where I want all my key information located. The player names and salary are from players that are playing games that night. The information is pulled using VLOOKUP from FanDuel sheet. The FanDuel sheet’s source is another excel workbook where I copy and paste the nightly CSV page.

All other sheets in the workbook are their own individual power query from a separate website.

On the Mastersheet, you will notice cell I23 is blank. (There are more blanks of players not on the stat sheet due to injury.) On the NumberFire sheet you will see CJ McCollum is projected 35.68 minutes but VLOOKUP doesn’t see this because on the Mastersheet it’s listed as CJ as opposed to C.J. on NumberFire.
Also feel free to look around mess with the data some. I have a local copy saved in case something goes wrong.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

is that what you want?
example(!)
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
did you try that?
if not just try
what columns you need to add?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,427
Messages
5,601,605
Members
414,462
Latest member
StageRiis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top