Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 {Order Number} {Item} {Customer} and has the same first sheet template that I have attached an image of. There are many repeat Items (customer part #) with new Order #'s. The sheet name of 'RSC C' can be the same or different for all the other workbooks. In the 'transform sample file' tab in Power Query, I believe I have entered the function correctly to base off sheet index (0; first sheet). I'll attach an image to make sure I typed it in correctly.

 

What I want to do:

 

I'm trying to create a table that has every order we've done (repeat item orders included) that will display each order as a row with the applicable info. I'd like the label for each cell as the respective header, but I don't know how to transform my data/table accordingly. The information for each order I'd like to retrieve is: (w/ cell value below)

  • Customer Bill To (I'd like to make customer its own column w/ the two cells below combined into one so the headers would be Customer Bill To and Customer Bill To Address)
  • Customer Ship To (I'd like to do the same format as Bill To for Ship To. In this example CPU stands for 'Customer Pick Up'. Although, many of our other customers either ship to the same bill address or to one of their customers)
  • Customer Part # (this is Item)
  • Job #
  • Inside Dimensions (I would like to remove the X's between Length, Width, and Depth and have L W D as headers with their value, respectively)
  • Style
  • RSC
  • Joint 
  • BMC
  • INKS
  • Board Grade
  • Print Die # (include location to the right in seperate header 'Print Die Location')
  • Order QTY
  • # Out
  • Cutting Die #(include location to the right in seperate header 'Cutting Die Location')
  • Sheets Required
  • Over/Under
  • Printing
  • Pallets
  • Total MSF
  • Stack Pattern
  • CAD #
  • Blank Sheet Size (only the Length and Width)
  • Bands
  • #/Bundle
  • Order Date
  • # Load Tags
  • PCS/Unit
  • Special Notes (Notes can be in any row under the label 'Special Instructions', so I would like to be able to grab all of them if it is possible)
  • Image? (I'm not sure if it is possible to retrieve the image because it is not referenced in a cell, only placed on top of the sheet. If it is possible to grab the image then the image too)

 

Sample job first tab.pngTransform Sample Cust Info thru Col9.pngTransform Sample File col8-colend.pngTransform Sample File Sheet Index.png

After the table is created, I would analyze the output by using the cluster function to remove rows that have been misspelled due to human error but are the same item.

 

If there is any more information that you need to help me out, I would gladly attach or explain.

 

Thank you 

1 ACCEPTED SOLUTION

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

 

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

 

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

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.



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
Anonymous
Not applicable

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=xyp...

 

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

 

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

 

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

 

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

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.