cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
janzitniak
Frequent Visitor

How to extract particular cell values from several workbooks into rows

 

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

 

input-data-powerquery.jpg

 

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:

 

 

target-powerquery.jpg

 

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

Jan

2 ACCEPTED SOLUTIONS

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.

edhans_1-1619039948404.png

 

 

 

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Jan

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.

edhans_1-1619039948404.png

 

 

 

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors