Power query with data in difficult format

[mhessnm]

Hello, I'm trying to take a text file with data in the following format. The data begins like this:

OGRID: [373075] XTO PERMIAN OPERATING LLC.
Reference ID: fAPP2226958316
Reference Period: 202301
Days Reported: 18
Total Flared: 14,355 MCF
Total Vented: 0 MCF
OGRID: [373075] XTO PERMIAN OPERATING LLC.
Reference ID: fAPP2126356070
Reference Period: 202301
Days Reported: 4
Total Flared: 6,570 MCF
Total Vented: 0 MCF


The data repeats this format for about 1800 lines. I would like to use the repeating characters before the colon (OGRID, Reference ID, Reference Period, Days Reported, Total Flared, Total Vented) as column names and then place all the data behind the colon in the appropriate columns, so it looks like this:

OGRID	Reference ID	Reference Period	Days Reported	Total Flared	Total Vented
[373075] XTO PERMIAN OPERATING LLC.	fAPP2226958316	202301	18	14,355 MCF	0 MCF
[373075] XTO PERMIAN OPERATING LLC.	fAPP2126356070	202301	4	6,570 MCF	0 MCF

I have been able to separate the data by splitting the column using the colon as delimiter, but after that I'm stuck. I've tried Transform and Pivot Column, but if I tell it not to aggregate I get an error. I would appreciate any help I can get. Thank you!

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"Col1", "Col2"}),
    tbl1 = Table.Group(tbl, {"Col1"}, {{"Col2", each _[Col2]}}),
    Result = Table.FromColumns(tbl1[Col2], tbl1[Col1])
in
    Result

Book1
	A	B	C	D	E	F	G	H	I
1	Column1		OGRID	Reference ID	Reference Period	Days Reported	Total Flared	Total Vented
2	OGRID: [373075] XTO PERMIAN OPERATING LLC.		[373075] XTO PERMIAN OPERATING LLC.	fAPP2226958316	202301	18	14,355 MCF	0 MCF
3	Reference ID: fAPP2226958316		[373075] XTO PERMIAN OPERATING LLC.	fAPP2126356070	202301	4	6,570 MCF	0 MCF
4	Reference Period: 202301
5	Days Reported: 18
6	Total Flared: 14,355 MCF
7	Total Vented: 0 MCF
8	OGRID: [373075] XTO PERMIAN OPERATING LLC.
9	Reference ID: fAPP2126356070
10	Reference Period: 202301
11	Days Reported: 4
12	Total Flared: 6,570 MCF
13	Total Vented: 0 MCF
14
Sheet1

 

[mhessnm]

Hey JGordon11, that was awesome! I so appreciate your time! I don't understand it, because I'm not that advanced, but I'll study the syntax. Thanks again!

 

[JGordon11]

This way may be more useful. It gets rid of the MCF unit in the totals columns and turns the text into numbers that can be operated on (average, max, min, etc.).

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"Col1", "Col2"}),
    tbl1 = Table.Group(tbl, {"Col1"}, {{"Col2", each _[Col2]}}),
    tbl2 = Table.FromColumns(tbl1[Col2], tbl1[Col1]),
    tbl3 = Table.TransformColumns(tbl2, {{"Total Flared", each Number.From(Text.Replace(_, " MCF", ""))}, {"Total Vented", each Number.From(Text.Replace(_, " MCF", ""))}}),
    tbl4 = Table.RenameColumns(tbl3, {{"Total Flared", "Total Flared (MCF)"}, {"Total Vented", "Total Vented (MCF)"}}),
    Result = Table.TransformColumnTypes(tbl4,{{"Reference Period", Int64.Type}, {"Total Flared (MCF)", type number}, {"Total Vented (MCF)", type number}, {"Days Reported", Int64.Type}})
in
    Result

 

[mhessnm]

Oh, that's cool! Thank you! While I can understand what the query is doing, I want to make sure I understand how so this will give me a lot of things to study.