Forum

misaligned columns ...
 
Notifications
Clear all

misaligned columns in imported data

4 Posts
3 Users
0 Reactions
497 Views
(@dilan)
Posts: 2
New Member
Topic starter
 

I used power query to import data from a pdf file and noticed the data in the last row was misaligned. There seems to be two additional columns created to the far right and the columns where the data should be in appears as null.

Tried using conditional columns but unable to do so as the first two rows (see attachment) already have data in all the correct columns.

In excel you would simply delete the empty cells to move all data left but not sure how to do this in power query.

I'm sure it's a simple fix but I just can't seem to get my head around it.

thanks,

Dilan

 
Posted : 02/08/2020 8:53 am
(@dilan)
Posts: 2
New Member
Topic starter
 

Apologies, posted the questions without the attachment.

 
Posted : 02/08/2020 8:55 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Dilan,

Welcome to our forum!

One option is to duplicate the query: in the first query keep the rows that have null in the End Date column and in the second query remove the rows that have null in the End Date column.

Then you can remove the extra columns in the first query before merging it back to the second query.

Hope that makes sense.

Mynda

 
Posted : 03/08/2020 7:23 pm
(@bluesky63)
Posts: 162
Estimable Member
 

Hi Dilan,

add a custom column

Text.Combine(List.Transform(List.Skip(Record.FieldValues(_)),Text.From),",")

* Record.FieldValues() means fields with null,  this is what you want to remove

after Text.Combine =>  Peter,1/3/2020 12:00:00 am,31/12/2020 12:00:00 am,100000,7,3000,  then Split to column again with delimiter comma,  and Append with the header

 
Posted : 05/08/2020 5:31 am
Share: