Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

add column and cell reference in Power Query

Hello,    I am new to using Powery Query and was wondering if someone could help me out. I have a local folder on my desktop at work that has roughly 1000 excel workbooks. Each workbook is titled {...
General Comment
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    If I understand it correctly, you need to extract certain info from filename and add it to the table. If your filenames are all in the same structure, read from folder and add the 3 columns prior to read the binary to table then do Table.Combine. Do not use sample queries, and agree with edhans it is a project to re shape all your data

     

    let
    Source = Folder.Files("C:\Users\yourFolderPath"),
    #"AddJob#" = Table.AddColumn(Source, "JOB #", each Text.BeforeDelimiter([Name]," ")),
    #"AddCustPart#" = Table.AddColumn(#"AddJob#", "CUSTOMER PART #", each Text.BetweenDelimiters([Name]," "," ",1)),
    AddBillto = Table.AddColumn(#"AddCustPart#", "CUSTOMER BILL TO", each Text.BetweenDelimiters([Name]," ",".xlsx",2))
in
    AddBillto

     

edhans's avatar
edhans
Community Champion
5 years ago

You are going to need to break this down into managable pieces. Overall this is a consulting project, not a question on a specific issue. So start with a the first thing you are having problems 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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Sorry for the confusion. What I should've said is:

 

I work for a corrugated (cardboard) box/display manufacturing company. We receive new orders from customers through our email and manually input every order into our 'Incoming Orders' Google Sheet. In the Google sheet, we assign each new order with an order #. In that row, we fill out what item, quantity, due date, sheet size, Customer, PO $, PO #. After inputting the order into our Google Sheet, We...

  • Open files icon on our PC desktop and search our 'Orders' folder for excel files that have that Item # (Customer Part # in excel file). 
  • From the results, we find the most recent file (highest Job #) that is associated with that Item. We open the file that has the highest Job # because we know it is the most recent order and the file is up to date (notes, qty, any changes).
    • Example of file name: 23154 HW43x48 XXX Packaging
    • Order #: 23154; Item (Customer Part #): HW43x48; Customer: XXX Packaging
  • Then, we make a copy & rename this file (save to 'Orders' folder) and change the Order #, PO #, Order Date, Due Date, and updated QTY based on the needs of our customer on the first sheet.  
    • We're working on automating this process, as it is very repetitive. 
    • Our first sheet can be titled RSC, PAD, FOL, and a bunch more this is the value under the 'Style' header in the excel file 
  • After we enter the order in excel and save with the correct File Name, we print out the first sheet to give to our machine operators. 

Here are the files:

https://www.dropbox.com/scl/fi/eprv10fqfu8bpw8zumz2e/23381-DC4248-DAME-PACKAGING.xlsx?dl=0&rlkey=xypya3l7j1793q8ncfuw3mszd

 

https://www.dropbox.com/scl/fi/li53d4mlez1sgknkytgl7/PREFERRED-TABLE-OUTPUT.xlsx?dl=0&rlkey=qx18dtyamrwtsmox5sa2y2owy

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    If I understand it correctly, you need to extract certain info from filename and add it to the table. If your filenames are all in the same structure, read from folder and add the 3 columns prior to read the binary to table then do Table.Combine. Do not use sample queries, and agree with edhans it is a project to re shape all your data

     

    let
    Source = Folder.Files("C:\Users\yourFolderPath"),
    #"AddJob#" = Table.AddColumn(Source, "JOB #", each Text.BeforeDelimiter([Name]," ")),
    #"AddCustPart#" = Table.AddColumn(#"AddJob#", "CUSTOMER PART #", each Text.BetweenDelimiters([Name]," "," ",1)),
    AddBillto = Table.AddColumn(#"AddCustPart#", "CUSTOMER BILL TO", each Text.BetweenDelimiters([Name]," ",".xlsx",2))
in
    AddBillto

     

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    Hi @jbaxter 

     

    If I understand it correctly, you need to extract certain info from filename and add it to the table. If your filenames are all in the same structure, read from folder and add the 3 columns prior to read the binary to table then do Table.Combine. Do not use sample queries, and agree with @edhans it is a project to re shape all your data

     

    Vera_33_0-1627042694848.png

    let
    Source = Folder.Files("C:\Users\yourFolderPath"),
    #"AddJob#" = Table.AddColumn(Source, "JOB #", each Text.BeforeDelimiter([Name]," ")),
    #"AddCustPart#" = Table.AddColumn(#"AddJob#", "CUSTOMER PART #", each Text.BetweenDelimiters([Name]," "," ",1)),
    AddBillto = Table.AddColumn(#"AddCustPart#", "CUSTOMER BILL TO", each Text.BetweenDelimiters([Name]," ",".xlsx",2))
in
    AddBillto