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)
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
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
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
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
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
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
