How to extract particular cell values from several workbooks into rows

[janzitniak]

Hi guys,
I have several excel workbooks (invoices) in the folder. An invoice looks like this:

and I need extract particular values in cells (marked as red in the picture above) of all workbooks and get a table where will be data extracted in rows like this:

Example of the invoice is for available here.
Is it possible do this in the Power BI?
Regards and thank you in advance.

Jan

[edhans · Accepted solution]

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.

[edhans]

The example you provided is just a JPG, not a xlsx file. Can you provide an actual workbook to play with?

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[janzitniak]

Thank you for your feedback, I made changes in original post, so that file is available to download.

Jan

[edhans · Accepted solution]

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.

[Y_G]

Can we use this same code for a Transformed file that has multiple combined invoice excel sheets? or is there a loop function in M code that parses through different cell values? 

[edhans · Accepted solution]

@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.