Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |