Assistance Needed with Removing Extra Rows and Deleting Spaces in Power Query

[itsgrady]

I am seeking your help regarding a challenge I am facing with Power Query.

I regularly import a file into Power Query as an Excel Table. Unfortunately, this file always contains two extra rows at the bottom that I need to remove. I would like to automate this process using Power Query, but I am unsure of the specific steps to take. Could you kindly guide me on how to remove these extra rows through Power Query?

Furthermore, within the Excel file, there is a column with data. However, all the empty cells in this column contain two spaces. I would like to delete these spaces in the empty cells using Power Query. I currently perform a manual process after running the Query. I believe there must be an efficient way to achieve this using Power Query.

I would greatly appreciate it if you could assist me with these challenges. Any guidance or instructions you can provide would be immensely helpful.

Thank you very much for your time and help.

 

[alansidman]

To remove the bottom 2 rows.

= Table.RemoveLastN(#"Previous Step",2)

To remove the empty spaces and replace them with nulls.

= Table.ReplaceValue(#"Previous Step,"  ",null,Replacer.ReplaceValue,{"Column Name"})

 

[itsgrady]

To remove the bottom 2 rows.

= Table.RemoveLastN(#"Previous Step",2)

To remove the empty spaces and replace them with nulls.

= Table.ReplaceValue(#"Previous Step,"  ",null,Replacer.ReplaceValue,{"Column Name"})

Thanks for taking time to point me in a good direction on this Query problem. I like using Query, but I’m still learning my way through it. I appreciate the help!

 

[alansidman]

Get this book to move your learning curve along: Master Your Data with Power Query in Excel and Power BI

thanks for the feedback.