PowerQuery: while importing a text document, detect if a row is empty in a given column

[datatronics505]

Hi,
So I have this text document that I'd like to turn into a table via PowerQuery, plan to later turn that query into a custom function since the operation will be a regular occurence and am already stuck at step 1.
The text file looks like this:

NAME AND PLACE OF BIRTH STATE DATE
TYPE OF OFFER
NAME OF OFFER
SPECIAL REMARKS

Georgie St. Augustine FL 02/11/1982
Managerial
Cash-a-lot
Big money

When imported into PowerQuery window, the first line is correctly detected as titles for separate columns and the table is formatted as such. The problem is, all of the lines that seem to belong to column 1 ("NAME AND PLACE OF BIRTH") and are all capital letters should also be in their own separate column (TYPE OF OFFER should be its own column, NAME OF OFFER another column etc.). There is a double newline separator in the text file between the last all-caps line and the first line below that separator ("Georgie St. Augustine(tab) FL(tab)02/11/1982") but in the query there's only one row empty between all caps and small caps text.
How do I detect for a presence of an empty row in "NAME AND PLACE OF BIRTH" and potentially be able to use it as a delimiter for more new columns?

 

[alansidman]

Visuals really help. Can you show some sample data (8-10 records) using XL2BB and then show a mock up of what you expect the results to look like.

 

[datatronics505]

This is what the end result should look like, I had to insert delimiters myself to make this output possible. The text file from which this should be derived is already given in my original post.

 

[jdellasala]

View attachment 80494
This is what the end result should look like, I had to insert delimiters myself to make this output possible. The text file from which this should be derived is already given in my original post.

Name and Place of Birth in the same column? Are there more than one items in the Text file? Did you make the column names? I ask because it would help if there were no spaces in the column names. Attach a sample text file.

 

[alansidman]

Based upon the limited data presented, here is a power Query solution. This solution assumes that all data is in the format presented and is representative of your actual data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1.1", "Column1.1.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column1.1.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1.2.1", "Column1.1.2.2", "Column1.1.2.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Column1.1.2.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Column1.1.1", "Column1.1.2.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Name and Place of Birth"}, {"Column1.1.2.3", "State"}, {"Column1.2", "Date"}, {"Column2", "Type of Offer"}, {"Column3", "Name of Author"}, {"Column4", "Special Remarks"}})
in
    #"Renamed Columns"

 

[datatronics505]

Name and Place of Birth in the same column? Are there more than one items in the Text file? Did you make the column names? I ask because it would help if there were no spaces in the column names. Attach a sample text file.

Yes, name and place of birth in the same column. Yes spaces in column names. This is the format in which raw text arrives. And yes on usage of different delimiters for the same thing in a single file. If that helps, here's the sample text file:
NAME AND PLACE OF BIRTH STATE DATE
TYPE OF OFFER
NAME OF OFFER
SPECIAL REMARKS

Georgie St. Augustine FL 02/11/1982
Managerial
Cash-a-lot
Big money

Also read the opening post where I described what nonprinitng character goes where, first line is tab-delimited, second line and subsequent lines with all caps should be a continuance of the first line to be column headers, there's line feed twice between all caps and small caps text.

 

[alansidman]

Misread your original post. Disregard my post above with PQ/.

 

[jdellasala]

Yes, name and place of birth in the same column. Yes spaces in column names. This is the format in which raw text arrives. And yes on usage of different delimiters for the same thing in a single file. If that helps, here's the sample text file:
NAME AND PLACE OF BIRTH STATE DATE
TYPE OF OFFER
NAME OF OFFER
SPECIAL REMARKS

Georgie St. Augustine FL 02/11/1982
Managerial
Cash-a-lot
Big money

Also read the opening post where I described what nonprinitng character goes where, first line is tab-delimited, second line and subsequent lines with all caps should be a continuance of the first line to be column headers, there's line feed twice between all caps and small caps text.

Not tab delimited when posting. A copy of the actual file would be more helpful. Also, are the column headers in all the files? Are there multiple files? If there are, are they all in the same directory?

 

[datatronics505]

Based upon the limited data presented, here is a power Query solution. This solution assumes that all data is in the format presented and is representative of your actual data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1.1", "Column1.1.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column1.1.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1.2.1", "Column1.1.2.2", "Column1.1.2.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Column1.1.2.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Column1.1.1", "Column1.1.2.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Name and Place of Birth"}, {"Column1.1.2.3", "State"}, {"Column1.2", "Date"}, {"Column2", "Type of Offer"}, {"Column3", "Name of Author"}, {"Column4", "Special Remarks"}})
in
    #"Renamed Columns"

Not tab delimited when posting. A copy of the actual file would be more helpful. Also, are the column headers in all the files? Are there multiple files? If there are, are they all in the same directory?

Assume all future files follow this format and they all land in the same directory, and all headers are in all the future files. I know how to use XL2BB but you need the actual text file... there's no button for attaching text files here, just pictures and for enclosing code in different formats.

 

[datatronics505]

Misread your original post. Disregard my post above with PQ/.

Thank you for your help. Your code isn't THAT unusable.