Log In
Register
Home
New Member
August 1, 2020
Offline
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
New Member
August 1, 2020
Offline
Apologies, posted the questions without the attachment.
July 16, 2010
Offline
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
August 21, 2019
Offline
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
1 Guest(s)
