Promoting first row as headers (Power Query) - Minor issue

[stockmarket123]

Hi there

I am using Power Query to import some standard customer payment reports (csv format) and then clean them up further.

The customer payment reports would be in the following format: The first 6 rows would describe the payment file in general (eg. Payment reference #:, date of deposit:, payee number:, etc) followed by multiple rows of all the invoices and $ amounts of the invoices.

After using a conditional column to extract the "payment reference#" and the "date of deposit" into new columns, I promote the first row as headers so that my columns are properly named.

The issue I am having: Since the payment reference # and the date of deposit are already shown as headers, it gets replaced by the actual data itself. For example, "1020362" and "30-JUL-2018" would show up in the column header incorrectly. All the other columns have the correct header information (eg. Invoice No., Invoice Amt) except these 2 aforementioned columns.

Is there any workaround solution? Please keep in mind I am not a coder by any means, so hopefully the solution is within the actual Power Query GUI.

Thank you

Winston

 

[sandy666]

IMHO you can remove all rows from the top except data rows, then create you conditional column(s) and manually name all columns.
or
create helper table with old headers and new headers the append this table to your data table. It will give you possibilty to change headers as you wish

 

[stockmarket123]

Thank you...However, If I remove all rows from top except data rows, then I would not be able to create conditional columns because I still require still require some of the information contained in those general information rows (first 6 rows), including the payment reference # and the date of deposit

 

[sandy666]

is it possible to share desensitized excel file with eg. 10 rows of source? or even paste here as table.

 

[stockmarket123]

Hi Sandy

Kindly, do you know how to attach an excel file?...I've looked through all the icons on the REPLY box, but nothing allows for attachments...I know I am just a newb to this site.

 

[sandy666]

Put your desensitized excel file on GoogleDrive or OneDrive or any other, share it to be downloadable and paste link here.

or you can use tinyupload.com

 

[stockmarket123]

okay....Here is a link to the test file (with the query called GLS Payments).

https://drive.google.com/open?id=1vS3nSlm4RAWzL7hDt68y_cl5xjxPjTNR

Once you open up the file, you can see the general information in rows 1-5 (Payee Name, ID, Number, Pay Date, etc). I added two conditional columns so that the Payment Number, and Pay Date have their own separate columns. The trick is once I delete rows 1-6, and promote the first line to headers, then I get some of the data in Columns 23 and 24 promoted as headers incorrectly

Please let me know if you need further info

 

[sandy666]

Ok, I got it
you can delete it from GD if you want

 

[stockmarket123]

okay...thank you!!!

 

[sandy666]

Because I can do nothing without source, I added a helper table
- Append HelperTable with your GLS PAYMENTS table then promote headers (HelperTable should be the first)

https://1drv.ms/x/s!AopTvf3RGAN5kBHqag4VcTj4cDcf