Data cleaning help

[rmcgrath]

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

[speedramps · Accepted solution]

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