Hello EDNA - needed support in Power Q . The attached is a summarized version of an ms project. it includes the current state and desired result tables. also included the excel file.
- File name as a column
- Major task description, copied down each subtask
- copy down each S , W and Craft (entered manually for each major line., but need it copied down for each minor/subtask. see pics and attached.
3rdWPasSummaryV.xlsx (25.4 KB)
InEProj.pbix (106.6 KB)
Thank you for any and all help in advance, 
Frankee
Hello @Frankee,
Thank you for posting your questions on the Community Forum. It appears that you are seeking assistance in designing your model file. Specifically, you have inquired about Fill Down and adding a Custom Column in Power Query. We are happy to help and would like to provide you with additional information on how to utilize these techniques. However, before we proceed, we want to ensure that we understand the scope of your request.
We noticed that your model file only consists of imported tables from your Excel source file. There are no transformations, relationships, or measures added, and a dedicated date table is missing. We would like to confirm whether this is an export from MS Project and if you have a subscription to MS Project Online. If so, there are several Power BI Templates that are specifically designed for this purpose.
Please let us how we can assist you further.
@ystroman - thanks for getting back to me. Correct. This is not the model, but only a light summary. Looking to mimic the steps on the actual 50 MB model. The “Current” table is the table that i need to transform.
Currently don’t have ms project online access.
I am curious about these templates you mentioned…but if they require ms project online services, i wouldn’t be able to take advantage of them.
If you can share some off line template info. I can review.
@Frankee, I was looking for previous models designed to leverage the desktop MS Project file; however, I have not had much time to spend on it. The templates are oriented towards the present subscription offering, with storage in Dataverse. I will pull together a list of the URLs to look to for different PowerBI Templates in several hours and will add them to a reply to this Post. Which version of Project are you currently using, and is Project Server an option? Is this a one-time migration, or are you endeavoring to build a sustainable reporting effort?
@ystroman - the latest version for desktop use. No project server is available available. Im getting this excel extract from the customer.
Looking to build a sustainable effort. The general idea, is to place all these project extracts into sharepoint folder. Per file , I will manually add the 6 right most columns…potentially adding more in the future.
Thank you,
Frankee
Hello there @Frankee,
I was thinking about the project you mentioned where MS Project Excel Extracts are to be stored as an attachment to a Sharepoint List, and I came up with an alternative approach that you may find interesting. Instead of attaching a Plan Status File, have you considered Syncing MS Project with SharePoint? With some customization, one or more Lists within a SharePoint Online Site can maintain a synchronized copy of the Task List. I’ve used this method in the past with great success. Another benefit of this approach is that it acts as a bridge between Project and Power BI in situations where Project for the Web or Project Server is unavailable. Let me know if this approach interests you; I’m happy to help further!
Reference Section with URLs:
The following articles cover the topic in a way that would add meaning:
How to Sync MS Project with SharePoint that focuses on using the OOTB ‘Tasks’ web part
Do follow the Video, and in particular, the guidance on using the .mpp file stored in Site Assets as a replacement for the .mpp file used now. That will ensure the Sync happens smoothly.
Brightworks (mentioned as well) expands on the idea and has a free Site Template for Project Management.
Hey @ystroman - thank you for sharing these resources. I will review. For now, let us continue .
I would like to continue with my initial request as it will be come critical and still need to build this out. w/ measures and the link.
very much interested in your approach.
Again, I will review what you shared.
Thank you,
Frankee
Hi @Frankee, attached is the Excel file with Filename Added that was taken from the Excel file and the three columns filled down. I did not see any other questions or requests from your Original Post. For the Desc column, which I manually entered on the feed worksheet. What I would suggest is to have two separate worksheets (one project extract and one WBS and custom columns (S, W, Craft) and merge the two sheets in a power query on a unique identifier that would give you the summary. Much of this is a requirements question, ease of handling, and what file metadata you need to store and use in one or more queries. The result of this is an intermediate worksheet. Let me know if you need any additional help with the request, and as well looking forward.
Prepared File:
Project Extract (Add Filename, Fill Down S,W,Craft).xlsx (27.5 KB)
Source File:
3rdWPasSummaryV.xlsx (16.5 KB)
