Power Query problem

[mattbrice]

I need to do a transformation on a table that I not sure how to accomplish.  

Monthly we download a csv file from our credit card vendor that is then turned around and uploaded into our accounting system.  The csv consists of one header row with invoice header information and all the remaining rows are detail line item rows for the resepctive invoice.  

What I have been tasked with is to transform the file so that each line item Detail row is its own invoice.  So instead of having one header row and numerous detail lines, I want one header row for each line item detail.

First column in each row identifies it as either a 'H'eader row, or 'D'etail row.  Header row has invoice number (Column C) which needs to have number updated with a -1, -2... -N (depending on number of detail lines).  Header also has sum of details (Column N) which needs to be updated to be same number as respective detail line.

Only thing that needs to change on detail line is update Invoice number (Column C) to match Header invoice number.

Before:

After:

Here is link with the sample data as shown. 

Sample_Data

Thanks for any help.

[v-xjiin-msft · Accepted solution]

Hi @mattbrice,

Based on your requirement, I have made a sample report. Please make a reference here, hope it helps.

Thanks,
Xi Jin.

[mattbrice]

@v-xjiin-msft thank you very much.  It worked perfectly.

Matt