polishing up a project

[Joseph Horling]

Hi Chandoo Experts,
Hope all is well and staying safe. I need to ask you a favor. Can you update the formula in the first name column so I can get a space between Mr./Dr./Miss etc and the first name? Also, in the last name column I would like a hypen between the last two names. These are complex formulas that someone gave me, but its exactly how I wanted it to look like. Not sure how to go about putting in the formula to do those things. Thanks for your help as always. Joe from Michigan.

 

[AlanSidman]

If you are willing to use Power Query/Get and Transform

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First", type text}, {"Middle", type text}, {"Last Name", type text}, {"Address", type text}, {"Town", type text}, {"State", type text}, {"Zip", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",".",". ",Replacer.ReplaceText,{"First"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," ","-",Replacer.ReplaceText,{"Last Name"})
in
    #"Replaced Value1"

 

[Joseph Horling]

I have the power query tab, but not sure where to put the code at.

 

[AlanSidman]

Have a look here--> https://www.howtoexcel.org/power-query/the-complete-guide-to-power-query/

 

[Joseph Horling]

Hi Alan,

I was following the steps of the power query guide and I got to the part where I loaded the table onto the a new sheet. Then I got to the point where the power query editor popped up, but don't understand where to go next. If you got the time today, can you go through this with me? It looks like it will work. Thanks, Joe.

 

[AlanSidman]

With PQ open, on the Home Tab, click on Advanced Editor. Highlight the code I provided. Insert it into the Advanced Editor replacing the existing code Make sure that the table name you used replaces the my "Table1" in the code.

 

[Joseph Horling]

Cool, got it! It doesn't seem to update when I put another item in the text. When I put Miss Sophia it comes out as "MissSophia." Can I update the code if I need to update my column? Thanks so much.

 

[AlanSidman]

In your original data, make your changes. Then, with your information from PQ in Excel, click on Refresh and it should update your output.

https://support.office.com/en-us/ar...in-excel-1524175f-777a-48fc-8fc7-c8514b984440

 

[Joseph Horling]

Hi Alan,

Could you check out the file with the quary? It doesn't seem to be refreshing or updating. Thank you.

 

[AlanSidman]

I believe that the answer is that all changes need to be made in the original file that you loaded from and not from the file you attached. That is the result file. The link to the query that was loaded is to the original document. Then click on the refresh in the output file.

 

[Joseph Horling]

Hi Alan,
I guess I don't understand the Power Quary. Is there anyway that you can update the formula to put the prefix in the first column without ruining the rest? Also can you add a formula to put a hyphen in between the two last names in the last name column? Thanks so much. Joe. Attached is the file.

 

[AlanSidman]

Power Query is not designed to add new data. That is done in the original file. PQ is for re-arranging data, cleansing data. In your example, how would one know which prefix to add. Why wouldn't you do it in Excel? And in Excel, what logic would you use to add it. I believe that I have already given you the added hyphen in the earlier PQ. It appeared in this line of code

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," ","-",Replacer.ReplaceText,{"Last Name"})

If you want to get proficient at PQ, then I suggest you pick up a copy of "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. Will really help you to understand this PQ and make your life a lot easier than trying to pick it up piecemeal.