Aligning Data Rows in Power Query (Removing different rows in different columns)

[Anonymous]

In the attached screenshot, I've been able to filter out key data needed from a Text document.

Current Results

However, each piece of data is in it's own row. I'm stuck trying to figure out how to realign these rows so that the 3 pieces of data appear in the same row, or simply to remove the Null values in each Column so that when each individual column is cleaned up, each row will contain the Letter ID, Account Number, and Name in a single row.

So instead of
[null] | Account # | [null]

Letter_ID | [null] | [null]

[null] | [null] | Name

I would get this:
Letter_ID | Account # | Name
Letter_ID | Account # | Name

[djurecic]

Hi @Anonymous ,

 Glad you got it to work. I made the mistake of assuming that you were looking for an automated way to solve. Will keep this in my back pocket in case I ever need it.

[djurecic]

Hi @Anonymous ,

 Maybe someone else will have an answer, but I don't see how this is possible. How would Power Query know which rows are part of the same record?

[Anonymous · Accepted solution]

I did find a solution:

https://community.powerbi.com/t5/Desktop/Remove-rows-of-certain-columns-with-Query/m-p/359476

In my case, I skipped the Source line, and added the code to the end. Making sure to update the references to the last line in my existing query.

I was struggling to get this to tack on to the end of my existing Query by adding the linked code in the Advanced Editor. I ultimately realized that my issue was that I did not update the previous step references.

Ultimately running through the code once moved Columns 1 & 3 up by one row so that now 1 & 2 aligned.

I added a second round of the code, giving them new variable names (e.g. TableToColumns2) and running those off of the "Reassmble" result, and this moved Column 3 up.

Data is aligned exactly as I need.

I appreciate the willingness to help!