How to extract particular cell values from several workbooks into rows

See if this works janzitniak 
Here is what it returns - of course the 1st row would have actual values and not "Name", "Address", etc., and you need to set your data types.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DateRow = 
        List.PositionOf(
            Table.TransformColumns(
                Source, 
                {
                    {"Column4", each Text.Start(Text.From(_, "en-US"), 4), type text}
                }
            )[Column4],
        "DATE"
        ),
    BillToRow = 
        List.PositionOf(
            Source[Column1],
            "Bill To:"
            ),
    DataList = 
        {
            Date.FromText(
                Text.AfterDelimiter(Source[Column4]{DateRow}, "DATE:")
            ),
            Number.FromText(
                Text.AfterDelimiter(Source[Column4]{DateRow + 1}, "INVOICE NUMBER")
            ),
            Source[Column1]{BillToRow + 1},
            Source[Column1]{BillToRow + 2},
            Source[Column1]{BillToRow + 3},
            Source[Column1]{BillToRow + 4},
            Source[Column1]{BillToRow + 5}
        },
    RowList = {"Date", "Invoice Number", "Name", "Company", "Address", "City State Zip", "Phone"},
    Result = Table.FromColumns({RowList, DataList}, {"Item", "Value"}),
    #"Transposed Table" = Table.Transpose(Result),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

 Here is your file back.

You can use this code from inside Power BI after the initial connection to an Excel file. I did it in the Excel file for convenience. Here are instructions to move this code successfully to another file. You'd get rid of the data after Source = in the above and replace with the series of navigation steps to get it in to Power BI. As such, all "Source" statements throughout the query might need to be replaced with the last step in Power BI that gets the relevant data. Remove any Changed Rows or Promoted Headers steps. This needs the raw data.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

EDIT: Final Note: Power Query is case sensitive, so when am looking for DATE and Bill To:, it will not find "Date" or "BILL TO:"

If that is a potential issue, set them to all upper or lower case first in columns 1 and 4, and then adjust the code as necessary.

janzitniak was this helpful? If so can you mark it as the solution. If not, post additional feedback on what you are tryingn to do.