Power Query Data Clean Up

[rizon69]

Hi,

I have been trying to get data from a Text file (See Input Table) into a structured table..(see Output Table).

My current issue is that the data also fetches the Adherence summary section(In Red, this is the subtotal over the above data).

All I need is the detail in Blue, the red section must be ignored.

Input:

 From: 04/01/16                                              IEX TotalView                                            Detail and Summary
   To: 04/21/16                                        Schedule Adherence Report                                                        
Shift:  0 All Day                                           Friday 04/22/16                                                    Page:   2


Printed: 16:38 04/22/16                                  By: Demo, User                                              Station: 476


[COLOR=#0000ff]MU: 702 Campaign[/COLOR]




Sorted by: Name  Activity Matching Range: 00:00   From/To Variance: 00:00   Activity Duration Variance: 00:00   
Report Across Agent Moves: No  Report Agent Moves: No




                                                                [COLOR=#0000ff]Team: Team 2[/COLOR]




[COLOR=#0000ff]13325 Surname1, Name1[/COLOR] - Adherence Summary


                                                      -------- Adherence ---------     ------------------ Conformance ------------------
Scheduled                  Scheduled    Actual        Min. In    Min.Out  Perc. In     +/- Min.    Perc. In    Percent of    Percent of 
Activities                   Time        Time          Adhere     Adhere    Adhere      Conform     Conform   Total Sched.  Total Actual
--------------------      ----------  ----------     --------   --------    ------    ---------    --------   ------------  ------------
====================      ==========  ==========     ========   ========    ======    =========
Total                           0:00        0:00            0          0       0 %           +0




[COLOR=#0000ff]Date: 04/01/16[/COLOR] - Adherence Summary


                                                      -------- Adherence ---------     ------------------ Conformance ------------------
Scheduled                  Scheduled    Actual        Min. In    Min.Out  Perc. In     +/- Min.    Perc. In    Percent of    Percent of 
Activities                   Time        Time          Adhere     Adhere    Adhere      Conform     Conform   Total Sched.  Total Actual
--------------------      ----------  ----------     --------   --------    ------    ---------    --------   ------------  ------------
[COLOR=#0000ff]Logout                         10:00       10:00          600          0     100 %           +0       100 %          100 %         100 [/COLOR]%
====================      ==========  ==========     ========   ========    ======    =========
[COLOR=#0000ff]Total                          10:00       10:00          600          0     100 %           +0[/COLOR]




[COLOR=#0000ff]Date: 04/02/16 [/COLOR]- Adherence Summary


                                                      -------- Adherence ---------     ------------------ Conformance ------------------
Scheduled                  Scheduled    Actual        Min. In    Min.Out  Perc. In     +/- Min.    Perc. In    Percent of    Percent of 
Activities                   Time        Time          Adhere     Adhere    Adhere      Conform     Conform   Total Sched.  Total Actual
--------------------      ----------  ----------     --------   --------    ------    ---------    --------   ------------  ------------
[COLOR=#0000ff]Login                           9:00        0:00            0        540       0 %         -540         0 %           90 %           0 %
Logout                          1:00       10:00           60          0     100 %         +540      1000 %           10 %         100 %[/COLOR]
====================      ==========  ==========     ========   ========    ======    =========
[COLOR=#0000ff]Total                          10:00       10:00           60        540      10 %           +0[/COLOR]




[COLOR=#ff0000]Team:                      - Adherence Summary


                                                      -------- Adherence ---------     ------------------ Conformance ------------------
[/COLOR][COLOR=#ff0000]Scheduled                  Scheduled    Actual        Min. In    Min.Out  Perc. In     +/- Min.    Perc. In    Percent of    Percent of 
Activities                   Time        Time          Adhere     Adhere    Adhere      Conform     Conform   Total Sched.  Total Actual
--------------------      ----------  ----------     --------   --------    ------    ---------    --------   ------------  ------------
Login                           9:00        0:00            0        540       0 %         -540         0 %           45 %           0 %
Logout                         11:00       20:00          660          0     100 %         +540       182 %           55 %         100 %
====================      ==========  ==========     ========   ========    ======    =========
Total                          20:00       20:00          660        540      55 %           +0
[/COLOR]

____________________




 From: 04/01/16                                              IEX TotalView                                            Detail and Summary
   To: 04/21/16                                        Schedule Adherence Report                                                        
Shift:  0 All Day                                           Friday 04/22/16                                                    Page:   2


Printed: 16:38 04/22/16                                  By: Demo, User                                              Station: 476


[COLOR=#0000ff]MU: 702 Campaign
[/COLOR]



Sorted by: Name  Activity Matching Range: 00:00   From/To Variance: 00:00   Activity Duration Variance: 00:00   
Report Across Agent Moves: No  Report Agent Moves: No




                                                                Team: Team 1



[COLOR=#0000ff]
13326 Surname2, Name2 [/COLOR]- Adherence Summary


                                                      -------- Adherence ---------     ------------------ Conformance ------------------
Scheduled                  Scheduled    Actual        Min. In    Min.Out  Perc. In     +/- Min.    Perc. In    Percent of    Percent of 
Activities                   Time        Time          Adhere     Adhere    Adhere      Conform     Conform   Total Sched.  Total Actual
--------------------      ----------  ----------     --------   --------    ------    ---------    --------   ------------  ------------
====================      ==========  ==========     ========   ========    ======    =========
Total                           0:00        0:00            0          0       0 %           +0




[COLOR=#0000ff]Date: 04/01/16[/COLOR] - Adherence Summary


                                                      -------- Adherence ---------     ------------------ Conformance ------------------
Scheduled                  Scheduled    Actual        Min. In    Min.Out  Perc. In     +/- Min.    Perc. In    Percent of    Percent of 
Activities                   Time        Time          Adhere     Adhere    Adhere      Conform     Conform   Total Sched.  Total Actual
--------------------      ----------  ----------     --------   --------    ------    ---------    --------   ------------  ------------
[COLOR=#0000ff]Logout                         10:00       10:00          600          0     100 %           +0       100 %          100 %         100 %[/COLOR]
====================      ==========  ==========     ========   ========    ======    =========
[COLOR=#0000ff]Total                          10:00       10:00          600          0     100 %           +0[/COLOR]




[COLOR=#0000ff]Date: 04/02/16 [/COLOR]- Adherence Summary


                                                      -------- Adherence ---------     ------------------ Conformance ------------------
Scheduled                  Scheduled    Actual        Min. In    Min.Out  Perc. In     +/- Min.    Perc. In    Percent of    Percent of 
Activities                   Time        Time          Adhere     Adhere    Adhere      Conform     Conform   Total Sched.  Total Actual
--------------------      ----------  ----------     --------   --------    ------    ---------    --------   ------------  ------------
[COLOR=#0000ff]Login                           9:00        0:00            0        540       0 %         -540         0 %           90 %           0 %
Logout                          1:00       10:00           60          0     100 %         +540      1000 %           10 %         100 %[/COLOR]
====================      ==========  ==========     ========   ========    ======    =========
[COLOR=#0000ff]Total                          10:00       10:00           60        540      10 %           +0[/COLOR]




[COLOR=#ff0000]Team:                      - Adherence Summary


                                                      -------- Adherence ---------     ------------------ Conformance ------------------
Scheduled                  Scheduled    Actual        Min. In    Min.Out  Perc. In     +/- Min.    Perc. In    Percent of    Percent of 
Activities                   Time        Time          Adhere     Adhere    Adhere      Conform     Conform   Total Sched.  Total Actual
--------------------      ----------  ----------     --------   --------    ------    ---------    --------   ------------  ------------
Login                           9:00        0:00            0        540       0 %         -540         0 %           45 %           0 %
Logout                         11:00       20:00          660          0     100 %         +540       182 %           55 %         100 %
====================      ==========  ==========     ========   ========    ======    =========
Total                          20:00       20:00          660        540      55 %           +0[/COLOR]


____________________

Output:

 

[rizon69]

Bump,

is this possible.. I am sure this is possible

 

[billszysz]

Hi rizon,
Try this code below.... you have to change path to the txt file

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\....\YourFileName.txt"),null,null,1250)}),
    Filter = Table.SelectRows(Source, each not Text.StartsWith([Column1], " From:") and not Text.StartsWith([Column1], "   To:") and not Text.StartsWith([Column1], "Shift:") and not Text.StartsWith([Column1], "Printed:")   ),
    Filter1 = Table.SelectRows(Filter, each ([Column1] <> "")),
    AddCol1 = Table.AddColumn(Filter1, "Team", each if Text.Contains([Column1], "   Team:") then Text.Trim([Column1]) else null),
    AddCol2 = Table.AddColumn(AddCol1, "MU", each if Text.StartsWith([Column1], "MU: ") then Text.Split(Text.Replace([Column1], "MU: ",""), " "){0} else null),
    AddCol3 = Table.AddColumn(AddCol2, "Eliminate", each if [Column1] = "Team:                      - Adherence Summary" then "Remove" else if [Column1] = "____________________" then "ok" else null),
    FillDown1 = Table.FillDown(AddCol3,{"Eliminate"}),
    Filter2 = Table.SelectRows(FillDown1, each ([Eliminate] <> "Remove")),
    RemCol = Table.RemoveColumns(Filter2,{"Eliminate"}),
    AddCol4 = Table.AddColumn(RemCol, "Date", each if Text.StartsWith([Column1], "Date:") and Text.Contains([Column1], "- Adherence Summary") then Text.Middle([Column1], 6, 8) else if [Column1] = "____________________" then "stop" else null),
    AddCol5 = Table.AddColumn(AddCol4, "ID", each if List.Contains({0..9},try Number.From(Text.Start([Column1],1)) otherwise "txt")   then Text.Replace([Column1], " - Adherence Summary", "") else null),
    SplitIDCol = Table.SplitColumn(AddCol5,"ID",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"ID", "EmployeeName"}),
    ChType = Table.TransformColumnTypes(SplitIDCol,{{"ID", type text}, {"EmployeeName", type text}}),
    FillDown2 = Table.FillDown(ChType,{"Team", "MU", "Date", "ID", "EmployeeName"}),
    Filter3 = Table.SelectRows(FillDown2, each ([Date] = "04/01/16" or [Date] = "04/02/16")),
    Filter4 = Table.SelectRows(Filter3, each not Text.StartsWith([Column1], "    ") and not Text.StartsWith([Column1], "Date:")),
    ChVal = Table.ReplaceValue(Filter4,"Team: ","",Replacer.ReplaceText,{"Team"}),
    Positions = {0} & List.Transform(Text.PositionOf(ChVal{2}[Column1], " -", 2), each _+1),
    Head1 = List.Transform(Splitter.SplitTextByPositions(Positions) (ChVal{0}[Column1]), each Text.Trim(_)),
    Head2 = List.Transform(Splitter.SplitTextByPositions(Positions) (ChVal{1}[Column1]), each Text.Trim(_)),
    Headers = List.Transform(List.Positions(Head1), each Head1{_} & " " & Head2{_}),
    Filtered = Table.SelectRows(ChVal, each (not Text.StartsWith([Column1], "---")) and (not Text.StartsWith([Column1], "===")) and (not Text.StartsWith([Column1], "Scheduled")) and (not Text.StartsWith([Column1], "Activities")) ),
    SplitColumn = Table.SplitColumn(Filtered,"Column1",Splitter.SplitTextByPositions(Positions),Headers  )
in
    SplitColumn

Regards

 

[rizon69]

Hi billszysz,

Thanks for replying!

looks like we making some progress with the above code but it seems its doing the opposite?(its returning the subtotals, I require the daily break downs).

If you look at the below screenshot its returning time > 10hours a day, it should never go over 10 - 12 hours a day per person, so this looks like its just returning the end of period number. It runs fine with the above example as we looking at just 2 days.

Also note team summary appearing in the out but that not a train smash I can live with that.(or I'll put an extra step to filter out)

can I send you the master file that holds this data ?

 

[rizon69]

Sorry I couldn't edit the post..

here's an example... rows 72 - 74 total 120 hours.. that is the total for the period.. I need the detail behind that number by person,team,date etc.

so hypothetically speaking I should see 10 hours "Total" split by "Login"/"Login Out" for 12 days by person,team,date etc.


Thanks in Advance!

 

[billszysz]

As You could seen, the data were not representative so the code may fail in other situations
But..... try to change only one step - "AddCol3"

 AddCol3 = Table.AddColumn(AddCol2, "Eliminate", each if Text.StartsWith([Column1], "Team:    ") and Text.Contains([Column1], "   - Adherence Summary") then "Remove" else if Text.StartsWith([Column1], "_____") then "ok" else null),

If we have a luck, this should help.

Regards

 

[rizon69]

hmm.. the code still seems to return the subtotals(duration over 10 - 12 hrs) instead of the daily split, in this case am I right in saying that this solution would work best for data that's run one day at a time as the Subtotals and daily split would be exactly the same ?

Cheers

 

[billszysz]

Sorry @rizon but without the part of raw data containing the total for the period, i can not help. Everything depends on the shape of this data.
Regards

 

[rizon69]

Sorry @rizon but without the part of raw data containing the total for the period, i can not help. Everything depends on the shape of this data.
Regards

No Worries, thanks for the help.. you have given me a starting point, I will give this some more thought.. do let me know if another solution comes up..