Correct Imported Names from Power Query

[kohltonyork]

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!

 

[sandy666]

the question is about vlookup or power query?

 

[kohltonyork]

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]

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)

 

[kohltonyork]

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)

FD Sheet copy.xlsx

1drv.ms

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]

FD Sheet copy.xlsx

1drv.ms

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]

is that what you want?
example(!)

s666_PQ-NBA.xlsx

Microsoft Excel Workbook

1drv.ms

 

[kohltonyork]

is that what you want?
example(!)

s666_PQ-NBA.xlsx

Microsoft Excel Workbook

1drv.ms

I believe this looks great. Is this expandable if I want to add more columns to the master sheet?

 

[sandy666]

did you try that?
if not just try
what columns you need to add?