Promoting First Line to Headers (Minor Issue) - Power Query

[stockmarket123]

Hi there

I am using Power Query to import some standardized 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.

I am also attaching a sample file (TEST COMPANY), and you can drill down to the actual query model (GLS Payments) to see further.View attachment TEST COMPANY PAYMENT TEMPLATE.xlsx

Thank you

 

[AliGW]

Unfortunately, the data source for the query is reported as missing, so the query won't run. You need to provide a test file that has everything in that one file.

 

[stockmarket123]

HI Ali

Okay...I have revised the file now. Please find it within this google drive link:

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

Once you download, and open up the file, you can see that if I were to remove the first 6 rows, and then click on "Promote first Line as Headers", it would promote the data points, "22222" and "7/23/2018" as headers incorrectly

I would like to retain "Payment Number" and Payment Date" as the proper header information. All the other columns, 1-22 will have the correct headers

Please let me know if you have any other questions

winston

 

[stockmarket123]

another thing...thanks for even looking at my file...been staring at with glazed eyes for the longest time

 

[AliGW]

Why a Google link? We have a perfectly good attach file system here. Please use it. Thanks.

 

[AliGW]

I've had a look - you haven't shown the problem. What I want to see is the workbook with Power Query showing the problem so that I can see ALL the steps you have taken. If I try to replicate what you have done, I may end up with a different result. So, try attaching again, please, but this time take PQ to the point you mention in your post.

Also, can you manually mock up what you are trying to achieve - I cannot visualise the end result you're after.

 

[stockmarket123]

I've had a look - you haven't shown the problem. What I want to see is the workbook with Power Query showing the problem so that I can see ALL the steps you have taken. If I try to replicate what you have done, I may end up with a different result. So, try attaching again, please, but this time take PQ to the point you mention in your post.

Also, can you manually mock up what you are trying to achieve - I cannot visualise the end result you're after.

Okay...yep, attached it this time

Also, I have edited the power query model until the point the problem exists. Please look at the headers, and you can see that the final two columns have incorrect headers

Thank you

 

[NormS]

When faced with this type of file, I like to break it into its component pieces, then reassemble them the way I need them. You'll see that I created new queries for the payment number and payment date, then used those to create the additional columns.

Norm

Okay...yep, attached it this time

Also, I have edited the power query model until the point the problem exists. Please look at the headers, and you can see that the final two columns have incorrect headers

Thank you

 

[stockmarket123]

Thanks Norm for the help...very easy to follow solution