Power BI - Desktop - Parts follow up by phase

[Pynimisme]

Hello Power BI helpers,

I start on Power BI and I made researchs and trials to find a way to solve my problem without success.

I can easily do this on excel but with calculation on side tabs and I woul like to try on Power BI.

I have 179 lines on a excel file (179 different part designations) and for those parts, there is 4 different developpment phasis.

On each phasis there is "initial validation date" (data used to make a previsional graph), an "rescheduled validation date" if the initial date can't be reached and a "validation date" that show the date when the part is validated. There is 2 columns per dates, one with the format "DD/MM/YYYY" and one for weeks with the format "YYYY-WW".

There is also for each line by phasis a status of the part: 

- "OK"

- "OK with comments"

- "NOK"

- "Not seen"

I would like to have a graph (line and stacked column chart) showing the previsional delivery date by phase and by week as a line, "OK", "NOK", ... by week --> I manage to have a bar graph per week but the previsional date is not the same as I have by excel.

I created a table with CALENDARAUTO based on the MIN date and the MAX date of my excel file, I manage to have the same format in week also as I have on my excel file ("YYYY-WW") but I don't manage to make the link between my table "excel file" and the new table to use it as an X-Axis

As already mention I'm beginner with Power BI so I think the solution is very simple but currently I a little bit discouraged.

Most of the solutions I found is related to sales but I just want to know which part must be validated on what date and when it is validated.

I'm sorry in advance if it's to obvious and for also for my English (it's not my native language you may have noticed ;)).

Pynimisme

[Pynimisme]

Hello,

Thank you for the information.

Please find the link to the excel file, not a Power BI file, I hope it's fine: https://docs.google.com/spreadsheets/d/1gkLFmt4sCaKthvXIhmARHz_xjXFG7qdK/edit?usp=sharing&ouid=10783...

Let me know if it's OK with an excel file. Due to lack of knowledge on Power BI, I don't know if it would be easier to modify the excel file in order to have it compliant with Power BI "philosophy" or if I can deal with data directly with Power Query.

Thank you in advance for your help.

[lbendlin]

Link requires access.

[Pynimisme]

Hello,

First of all sorry for the access mistake, it should be OK now. I'm also sorry for the late reply, I hab business trip and I did'nt took time to answer you.

Thank you for your support.

[lbendlin]

Your data is not clean (you have duplicate column names for Phase 5, and inconsistent naming between final and finale and realised), so I would first clean it and then unpivot it to bring it into usable format. Since you have the date column I would drop the week column and then recreate it in the calendar table. Yeah, you have 5 phases, not 4...

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Classeur1.xlsx"), null, true),
    Tableau3_Table = Source{[Item="Tableau3",Kind="Table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(Tableau3_Table,{"Designation", "Phase 1 -#(lf)Status", "Phase 1 -#(lf)Initiale targeted date", "Phase 1 -#(lf)Rescheduled Date", "Phase 1 -#(lf)Finale Date", "Phase 2 -#(lf)Status ", "Phase 2 -#(lf)Initiale targeted date", "Phase 2 -#(lf)Rescheduled Date", "Phase 2 -#(lf)Finale Date", "Phase 3 - Status", "Phase 3 -#(lf)Initiale targeted date", "Phase 3 -#(lf)Rescheduled date", "Phase 3 -#(lf)Final date", "Phase 4 -#(lf)Status", "Phase 4 -#(lf)Initiale targeted date", "Phase 4 -#(lf)Rescheduled Date", "Phase 4 -#(lf)Finale Date", "Phase 5 - Status#(lf)", "Phase 5 -#(lf)Initiale targeted date", "Phase 5 -#(lf)Rescheduled week", "Phase 5 -#(lf)Realised date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Phase 5 -#(lf)Rescheduled week", "Phase 5 -#(lf)Rescheduled date"}, {"Phase 3 -#(lf)Final date", "Phase 3 -#(lf)Finale date"}, {"Phase 5 -#(lf)Realised date", "Phase 5 -#(lf)Finale date"}, {"Phase 1 -#(lf)Rescheduled Date", "Phase 1 -#(lf)Rescheduled date"}, {"Phase 1 -#(lf)Finale Date", "Phase 1 -#(lf)Finale date"}, {"Phase 2 -#(lf)Rescheduled Date", "Phase 2 -#(lf)Rescheduled date"}, {"Phase 2 -#(lf)Finale Date", "Phase 2 -#(lf)Finale date"}, {"Phase 4 -#(lf)Finale Date", "Phase 4 -#(lf)Finale date"}, {"Phase 4 -#(lf)Rescheduled Date", "Phase 4 -#(lf)Rescheduled date"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Designation"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Phase", "Attribute.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Phase", Text.Trim, type text}, {"Attribute.2", Text.Trim, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(#"Trimmed Text"[Attribute.2]), "Attribute.2", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Designation", type text}, {"Status", type text}, {"Initiale targeted date", type date}, {"Rescheduled date", type date}, {"Finale date", type date}})
in
    #"Changed Type"

After that, load into Power BI and add a calendar table. I chose to link to the Initiale target date but you can change that as needed.

Now you can start with the visualization.

I would like to have a graph (line and stacked column chart) showing the previsional delivery date by phase and by week as a line, "OK", "NOK", ... by week --> I manage to have a bar graph per week but the previsional date is not the same as I have by excel.

I don't understand how that would look like.  Do you have an example? 

See attached.

[Pynimisme · Accepted solution]

Hello,

Thank you for you quick feedback, you're right the fact that it's not clean, I missed that point when I extracted the real excel file to a "shareable" excel file.

I was not aware of unpivot function and I made some research and I guess that would be the best to implement, thank you for that!

I finally manage to do what I wanted yesterday by making a new table in power query with date and weeks at YYYY-WW format as you suggest.

Then I create new columns with a count rows of OK, NOK, ... by week (first time I tried, it didn't worked properly and then I tried again yesterday)

For the status new columns; I used the following calculation:

Phase 5 status NOK = CALCULATE(COUNTROWS(Tableau3),Tableau3[Phase 5 - Status]="NOK") 

I also create a new columns for the previsional date. I count the number of week occurrences equal to the column week based on Calendarauto (at the same format as on the excel file YYYY-WW)Objectif phase 5 = CALCULATE(COUNT(Tableau3[Phase 5 - Initiale targeted week]),ALLEXCEPT(DimDate,DimDate[Week])) 

For the visualisation I used a measure for each status:

Measure-NOK phase 5 Cumulative status - Phase 5 - Initiale targeted week = CALCULATE( [Measure-phase 5 - status NOK], FILTER( ALLSELECTED(DimDate[Week]), ISONORAFTER(DimDate[Week], MAX(DimDate[Week]), DESC) ) ) I made that for every status for phase 5 only.

For the previsional date, I used the following measure:

Measure-Cumulative Phase 5 prévi = CALCULATE(SUM(DimDate[objectif phase 5])/7,FILTER(all(DimDate),DimDate[Week]<=MAX(DimDate[Week]))) 

You will see below what it's look like:

You will find attached what it looks like -->In deep blue, OK status, in light blue, OK UC, in orange, NOK and in purple not seen. The pink line is the previsionnal date of OK status.

I think that I didn't explain well my issue, it was more about the status then the phasis. I should have keep only 1 phase to explain. It's my first time that I ask a question in such forum.

I will keep in mind for next time to take more time. It won't give you your time consumed back but I'm sorry for that.

Thank you for your support and I will search more about unpivot in the futur (I have lot to learn!).

Regards,

Pynimimse

 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...