Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
mattbrice
Solution Sage
Solution Sage

Power Query problem

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:

before.jpg

 

 

After:

after.jpg

 

 

Here is link with the sample data as shown. 

 

Sample_Data

 

Thanks for any help.

1 ACCEPTED SOLUTION
v-xjiin-msft
Solution Sage
Solution Sage

Hi @mattbrice,

 

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

 

Thanks,
Xi Jin.

View solution in original post

2 REPLIES 2
v-xjiin-msft
Solution Sage
Solution Sage

Hi @mattbrice,

 

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

 

Thanks,
Xi Jin.

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

 

Matt

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.