Forum Discussion
add column and cell reference in Power Query
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
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:
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