Forum Discussion
Solved
Data cleaning help
I am connecting to a folder of utility invoices (PDFs). The first table is how everything is pulled in to Power BI. The second is what I would like to get to after cleaning/scrubbing. Can you give me a good way to accomplish the 2nd table?
Source.Name | Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | Column10 |
| April 2024 Services.pdf | null | Dates | null | Reads | null | null | null | null | null | null |
| April 2024 Services.pdf | Meter# | From | To | From | To | Usage | Multiplier | Used | Units | Estimate |
| April 2024 Services.pdf | KZD86614917 | 04/02/24 | 05/02/24 | 1,780.562 | 1,851.644 | 71.081 | 2,100.00 | 149,270.940 | KVR | No |
| April 2024 Services.pdf | KZD86614917 | 04/02/24 | 05/02/24 | 4,248.941 | 4,527.792 | 278.850 | 2,100.00 | 585,586.680 | KWH | No |
| April 2024 Services.pdf | KZD86614917 | 04/02/24 | 04/02/24 | 1396.920 | 1.00 | 1396.920 | KW | No | ||
| Aug services2024.pdf | null | Dates | null | Reads | null | null | null | null | null | null |
| Aug services2024.pdf | Meter# | From | To | From | To | Usage | Multiplier | Used | Units | Estimate |
| Aug services2024.pdf | KZD86614917 | 08/02/24 | 09/03/24 | 2,176.500 | 2,296.221 | 119.720 | 2,100.00 | 251,413.890 | KVR | No |
| Aug services2024.pdf | KZD86614917 | 08/02/24 | 09/03/24 | 5,523.343 | 5,890.051 | 366.707 | 2,100.00 | 770,086.590 | KWH | No |
| Aug services2024.pdf | KZD86614917 | 08/06/24 | 08/06/24 | 1656.480 | 1.00 | 1656.480 | KW | No | ||
| DEC2023 SERVICES.pdf | null | Dates | null | Reads | null | null | null | null | null | null |
| DEC2023 SERVICES.pdf | Meter# | From | To | From | To | Usage | Multiplier | Used | Units | Estimate |
| DEC2023 SERVICES.pdf | KZD86614917 | 12/04/23 | 01/02/24 | 1,511.990 | 1,574.031 | 62.040 | 2,100.00 | 130,284.630 | KVR | No |
| DEC2023 SERVICES.pdf | KZD86614917 | 12/04/23 | 01/02/24 | 3,099.515 | 3,351.353 | 251.838 | 2,100.00 | 528,859.800 | KWH | No |
| DEC2023 SERVICES.pdf | KZD86614917 | 12/20/23 | 12/20/23 | 1229.760 | 1.00 | 1229.760 | KW | No |
| Source.Name | Meter# | From (Dates) | To | From (Reads) | To | Usage | Multiplier | Used | Units | Estimate |
| April 2024 Services.pdf | KZD86614917 | 04/02/24 | 05/02/24 | 1,780.562 | 1,851.644 | 71.081 | 2,100.00 | 149,270.940 | KVR | No |
| April 2024 Services.pdf | KZD86614917 | 04/02/24 | 05/02/24 | 4,248.941 | 4,527.792 | 278.850 | 2,100.00 | 585,586.680 | KWH | No |
| April 2024 Services.pdf | KZD86614917 | 04/02/24 | 04/02/24 | 1396.920 | 1.00 | 1396.920 | KW | No | ||
| Aug services2024.pdf | KZD86614917 | 08/02/24 | 09/03/24 | 2,176.500 | 2,296.221 | 119.720 | 2,100.00 | 251,413.890 | KVR | No |
| Aug services2024.pdf | KZD86614917 | 08/02/24 | 09/03/24 | 5,523.343 | 5,890.051 | 366.707 | 2,100.00 | 770,086.590 | KWH | No |
| Aug services2024.pdf | KZD86614917 | 08/06/24 | 08/06/24 | 1656.480 | 1.00 | 1656.480 | KW | No | ||
| DEC2023 SERVICES.pdf | KZD86614917 | 12/04/23 | 01/02/24 | 1,511.990 | 1,574.031 | 62.040 | 2,100.00 | 130,284.630 | KVR | No |
| DEC2023 SERVICES.pdf | KZD86614917 | 12/04/23 | 01/02/24 | 3,099.515 | 3,351.353 | 251.838 | 2,100.00 | 528,859.800 | KWH | No |
| DEC2023 SERVICES.pdf | KZD86614917 | 12/20/23 | 12/20/23 | 1229.760 | 1.00 | 1229.760 | KW | No |
In Power Query
Remove top 8 rows (adjust the number of rows depeding on your table)
Promote top row to header
Remove rows with date = null or To
Please click thumbs up because I have tried to help.
Then click accept solution if it works
1 Reply
In Power Query
Remove top 8 rows (adjust the number of rows depeding on your table)
Promote top row to header
Remove rows with date = null or To
Please click thumbs up because I have tried to help.
Then click accept solution if it works