• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula/Alternate Method for Flagging Documents Created and Approved by Same User

GraH - Guido -
Your M-code has marks of Pivot ... hmm?
(and still I don't use PowerQuery!)

With 'Pivot' there could find more details which 919budda didn't ask.
I found few why? ... but only he knows ... and he didn't ask those.

Without 'Pivot' filesize can be ~500kb. (Sometimes size matters...)
If select any cell from 'result-side' (blue columns) then could see filtered results.
(There could see those 'my why' and maybe those answers too.)
[CLR Filter]-btn 'clears filter' that it's possible to see ex those results.
 

Attachments

  • Sample2.xlsb
    502.3 KB · Views: 2
GraH,
not being familiar with Power Query (it's something I'll be getting my teeth into very soon), I was trying to find out why the following didn't show up in the power query results on first opening your file:
Travel Authorization:XYZUM0000NSLS
with Acting Employee ID: 40093779
It is on the Data sheet (in the vicinity of row 10444), but when I try and bring it up in the first step of the Power Query Editor I can't.
I'm told my version of Power Query (2.48.4792.941 32-bit) may be incompatible with your query, but the update button is greyed out.

Can you shed some light?
 
Last edited:
I've cocked up on that last message. Comparing results again:
XYZUM00003F2T 40101928
XYZUM0000UJIT 40183564
don't show up in your results. We both seem to have the same data for these two.

XYZUM000046VT doesn't show up in my results. We both seem to have the same data for this one too, and neither of us have this one with the status 'REVIEWED'.

The only differences I can find in our source data are that I have an extra two entries with blanks in the Acting Employee ID field:
Isidre Árias 02198275 XYZUM0000A8PR Jun 24, 2017 9:33:28 AM Dillan Menéndez ADJUSTED
and
Isidre Árias 02198275 XYZUM0000A8PR Jun 24, 2017 9:33:31 AM Dillan Menéndez RESERVATION CHANGES

but that shouldn't affect either of our results.

vletm's results are the same as mine but that may not be surprising since we use a method based on the same idea.
 
Last edited:
p45cal said:
GraH,
not being familiar with Power Query (it's something I'll be getting my teeth into very soon), I was trying to find out why the following didn't show up in the power query results on first opening your file:
Travel Authorization:XYZUM0000NSLS
with Acting Employee ID: 40093779
It is on the Data sheet (in the vicinity of row 10444), but when I try and bring it up in the first step of the Power Query Editor I can't.
I'm told my version of Power Query (2.48.4792.941 32-bit) may be incompatible with your query, but the update button is greyed out.

Can you shed some light?
Click to expand...
Preview window of PQ only shows first 1000 records (to save memory)...
workaround is to temporary set an index and filter on >1000 in this case.
Cannot have a look before this evening, since I don't have access to the workbook at work.
I'm using 64 bit at home. Nut sure if that is the "incompatibility issue", but might be. Don't know the version number by heart :)
 
I thought of trimming and cleaning the columns before filtering/pivoting, but decided not to. Could be a "format" issue. But only guessing now...
 
p45cal said:
XYZUM00003F2T 40101928
XYZUM0000UJIT 40183564
don't show up in your results. We both seem to have the same data for these two.
XYZUM000046VT doesn't show up in my results. We both seem to have the same data for this one too, and neither of us have this one with the status 'REVIEWED'.
Click to expand...
XYZUM00003F2T 40101928 and XYZUM0000UJIT 40183564: the reviewed status is done by some one else (perhaps I misread the requirements: I check if all 3 statuses are done by the same user. Using AND not OR)
The third one is caused by an error in the PQ... Where I fill up the records. The pivot makes a new row for each status, and I need to get all the data in the first row and then delete duplicates. Hmmm... Need to find the correct step in between to not make this error...
Do-able I think, by checking if each reference has at least all of these 3 statuses.
 
@919budda

I've read through the thread...
But I'm still unclear on exact criteria that should be used. And others are bit confused as well it looks like.

You'll need to provide exact criteria and examples from your uploaded data set.

For an example XYZUM00003F2T seemingly meets criteria, since there's 3 lines that has same acting employee# (Created, Authorization Signed, Reivewed). However, there is another Reviewer that also reviewed this travel auth#.

upload_2018-2-1_9-22-24.png
 
Hi p45cal, I believe Chihiro does a quit correct summary.
The PQ I provide is somewhat text book, and suited the initial request just fine. That has changed :).
This morning and noon I did not thoroughly check the data as you did (which is great btw). The differences you have spotted all are related to the fill up step. Powerful, but in this case a bit deceiving I admit!
Now given clear requirements those issues can be fixed. E.g. only use last reviewed and authorization signed would be selecting MAX from a group by transformation on the column date for each TAN per status.
It is again proof that the basic step of building a decent PQ is understanding the data pattern for 100%. If not, silly to fundamental mistakes are bound to happen. And spotting those is not always obvious I might add.
 
Hmm... Isn't next clear
As written in #1: What I want to flag are the travel authorizations that have a Status of Created, Authorization Signed, and Reviewed, all within the same Authorization Number and by the same Acting Employee ID.
Isn't that what would the result show?
Of course, there are ex few case (two or more times same of those and so on).
(#26) ... and he didn't ask those ... and do those matter? ... but only he knows!
I left in my 'Pivot'-version those visible and
also those would see from non-Pivot-version (#26).
 
vletm, yes that is clear. What is missing is what if there are double status records. It depends on how you approach it. Does it matter or not? It seems to be the reason you and p45cal versus me, have a different result.
Either way, both approaches can be applied. And indeed, up to 919budda. I believe he is pleased with both solutions.
Now if your solution is the correct one, then I need to update my PQ definition for sure.
 
GraH - Guido - case XYZUM000046VT and REVIEWD...
Could You check from which row did You find that 'REVIEWED'?
It's true that '40102560' has many 'REVIEWED'
but I didn't find any match with XYZUM000046VT.
Screen Shot 2018-02-01 at 21.44.48.png
 
vletm, in the last PQ I made I don't have "XYZUM000046VT" in the alert list neither.
Like I explained earlier, I got this first because of a "wrong" approach in PQ. The data pattern of life data is different frorm the sample.
To demonstrate, after the pivot the data looks like this:
upload_2018-2-2_8-12-25.png
You have a new row for each status. If you then fill up the ID's to have a single row of data, then in this case you can fill up the wrong ID. In the example above the last 3 columns would be like that.
In the new version I worked a bit around that, but still not 100% for the cases where you have returning status values. Like showed in #33.
As said in #35 the key in making PQ is managing and understanding the data pattern. If I have some time the coming days, I'll try to fix it.
 
GraH - Guido ... hmm ...
'my' after the pivot the data looks like this:
Screen Shot 2018-02-02 at 11.03.55.png
... and no matter of those bolded right side numbers.
> But without Pivot (#26) could get same results and
with 'right-side-shortcut-filtering'.

--- small steps --- with or without PQ --- gotta know, how to do it manually too ---​
 
Returning 26 records now...

Code: 
let
  Source = Excel.CurrentWorkbook(){[Name="tSource"]}[Content],
  #"Removed Columns" = Table.RemoveColumns(Source,{"Employee", "Employee ID", "Status Date/Time"}),
  #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Status] = "AUTHORIZATION SIGNED" or [Status] = "CREATED" or [Status] = "REVIEWED")),
  #"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
  #"Merged Columns" = Table.CombineColumns(#"Removed Duplicates",{"Travel Authorization Number", "Acting Employee"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"TAN - Acting"),
  #"Grouped Rows" = Table.Group(#"Merged Columns", {"TAN - Acting"}, {{"Count", each Table.RowCount(_), type number}, {"AllRows", each _, type table}}),
  #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each [Count] > 2),
  #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Count"}),
  #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns1", "AllRows", {"Acting Employee ID", "Status"}, {"Acting Employee ID", "Status"}),
  #"Pivoted Column" = Table.Pivot(#"Expanded AllRows", List.Distinct(#"Expanded AllRows"[Status]), "Status", "Acting Employee ID"),
  #"Removed Errors" = Table.RemoveRowsWithErrors(#"Pivoted Column"),
  #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Errors", "TAN - Acting", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"TAN - Acting.1", "TAN - Acting.2"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TAN - Acting.1", type text}, {"TAN - Acting.2", type text}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"TAN - Acting.2", "Acting Employee"}, {"TAN - Acting.1", "TAN"}})
in
  #"Renamed Columns"
 

Attachments

  • Copy of sample_223118.xlsx
    962.8 KB · Views: 0
You must log in or register to reply here.
Back
Top