Replace Value in Top Row

[ap072619]

I'm trying to load monthly invoices from pdf files and working on my Transform Sample File. The formatting is not Power Bi-friendly and stacks the invoice number and invoice date in consecutive rows. I need to move the date field up to the top row so that I can delete the bottom row and rename my columns. Example 1 is what it currently looks like, and example 2 is what I need it to look like. Is that possible?

[v-stephen-msft · Accepted solution]

Hi @ap072619 ,

You can add a custom column to return the pure number part first, and then compare it with Column3. If the custom column is equal to Column3, it means that the new line is not a date, and return null, otherwise return a date.

1.Add a custom column.

2.Fill up.

3.Filter out the Invoice Number, remove the unneeded columns.

Result:

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[watkinnc]

I would just change Column 3 to type date, and then replace the errors that you get with nulls (errors from dates not being converted to numbers) using the Replace Errors option via right click on the column. Then I would use the Fill Up function, which will replace the nulls with the date below it. Then you can just filter out the word "Date" from Column 2. That should do it!

--Nate

[ap072619]

Unfortunately, it just converts the other numbers to dates. I guess as a last-ditch effort, I can still use that and filter out the "wrong" dates, but that will get complicated since the wrong date will constantly change based on the invoice number.