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

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

Anonymous — Mon, 19 Dec 2022 15:56:38 GMT

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

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]

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

djurecic — Mon, 19 Dec 2022 18:37:48 GMT

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?

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

Anonymous — Tue, 20 Dec 2022 14:21:53 GMT

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!

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

djurecic — Tue, 20 Dec 2022 14:32:32 GMT

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.