Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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)
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
Solved! Go to 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
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry 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...
Here are the files:
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