Power Query - Column Status based on another column

[EbyEaso]

Hello Everyone,

I think somebody could help me out here. I have a report with dataset connected to Excel file. It has as table and content as shown above.

Ticket ID is unique and it has several Action Levels. Each action levels has status (Open or Closed). So Ticket ID will be approved only once all the Action Level status becomes closed. If any of the Action level is Open Ticket ID will not be approved.

So I need to find out whcih all Ticket ID has all the action level is closed but still status is not approved. So that I can tell stake holder to take action as it is pending for approval.

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately.)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFKBGLnnPzi1BQgQylWByqchCHsBBJOxi4M4vkXpOahCKZiqjUCstKQhR0LCoryy4BMuHQ6hi5nkGGFqMKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket ID" = _t, #"Action Level" = _t, #"Action Level Status" = _t, #"Ticket Status" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Ticket Status"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Ticket ID"}, {{"Temp", each _, type table [Ticket ID=nullable text, Action Level=nullable text, Action Level Status=nullable text, Ticket Status=nullable text]}}),
    //Function Start
    fxProcess=(Tbl)=>
        let
            #"Filled Down" = Table.FillDown(Tbl,{"Ticket Status"}),
            Custom2 = Table.ReplaceValue(#"Filled Down",each [Ticket Status],each if List.Count(List.Select(#"Filled Down"[Action Level Status], each _="Closed"))=Table.RowCount(#"Filled Down") and [Ticket Status]<>"Approved" then "To be Approved" else [Ticket Status],Replacer.ReplaceValue,{"Ticket Status"})
        in
            Custom2,
    //Function End
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Ticket ID", "Temp"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Ticket ID", "Action Level", "Action Level Status", "Ticket Status"}, {"Ticket ID", "Action Level", "Action Level Status", "Ticket Status"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Ticket Status] = "To be Approved")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Ticket ID"})
in
    #"Removed Duplicates"

[EbyEaso]

Hello @Vijay_A_Verma 

Thank you for the feedback. Appreciaet it.

I think I made a mistkae. Sorry for the confusion.

Ticket Status column is in a different table. It is manully updates once all the action level status is closed. So one of team member checks everyday if the action level is closed for all the action levels. if its all closed then he will update the ticket status as approved in other table.

So any owrk around? Sorry

[Vijay_A_Verma]

That is not an issue. We can bring ticket status in this table from the other table.

I need to know the table name of 2nd table and I also need to know the names of Ticket ID and Status columns...Basically, if you can post sample 2nd table, that would be great. 

[EbyEaso]

Thank You @Vijay_A_Verma 

ok. sure, let me wrap it put a new list and table for to explain.

Table  1 name - List

Table 2 Name - Action Items

So basically from Table 1 , Ticket ID 1 has 2 action title in Table 2 whcih is Closed. But Submit for review is "No"

Ticket ID 2 has 3 Action Item but only 2 are Closed. so Submit for review wont change. It should be "No"

Ticket ID 3 has 2 action item which is Closed and the Submit for review is already closed, which is good. no issue

What need to find out is identify Ticket ID from table 1 with Submit for review "No" which has all Action Item Closed in Table 2

So that we will know there is pending Ticket ID to change the Submit for review to Yes.

[EbyEaso · Accepted solution]

Hello,

I used Pivot-Unvpivot comlumns. Which I think is not perfect, but yes, atleast i got the output.

Thank You all. Thank You @Vijay_A_Verma