Data Cleaning First Rows power Query

[jmcph]

Hi, I would like to ask for help in cleaning my data in power query, my data looks like this: 

Date:                               Transaction No:                           Item:                             Amount:                 Cash:                Credit Card:

1/1/2020  4:20am                      1                                         null                                  1,000                  300                    700

1/1/2020  4:20am                      1                                         Product 1                         500
1/1/2020  4:20am                      1                                         Product 2                         300

1/1/2020  4:20am                      1                                         Product 3                         200

1/1/2020  4:20am                      1                                         null                                  null                     300                    700

1/5/2020  10:21pm                    2                                         null                                   5,000                  5,000         

1/5/2020  10:21pm                    2                                         Product 3                         5,000

I want to delete the FIRST ROW of every transaction no because it is just the total of the sale, however the last row of the transaction # is the payment made row which i will unpivot after deleting the FIRST ROW.

Also, i am also planning to analyze my data per hour , should i separate the date with time during the cleaning?

Thank you for any insights and help that you will provide! 

[HotChilli · Accepted solution]

The data did not copy and paste well so I can only offer some general advice.

To remove the first row of each transaction you could:

A) Add an index for each group of transactions then remove the Index = 1 records  or

B) It looks like the first record has an Item = null and Amount != null so you could use Table.SelectRows to construct a condition to keep the rows that don't match that.

I didn't understand what the last row (unpivot) should be doing.

To categorise by time you can use the 'Add Column' menu.  Go to the 'From Date and Time' section and choose Hour or Start of Hour etc.   You could relate the new column to a HourDimension table in powerbi front-end.

Good luck.