For each row of tmaster check tdetail table for a record Issue

Power Query
1

HI @Melissa ,

Its me again. Been a while. I have this scenario and I think there is a pattern already with this but I am struggling to understand how to accomplish it.

So basically I have two tables in the PBIX file and excel file named Tmaster and Tdetail.

Requirement

I basically want to add a new Status column in Tmaster table by scanning the Tmaster table, for each row of the Tmaster table check the Employee and the End date matches the Employee and Enddate of the Tdetail table and scan column LineItemApproavlStatus and check that all the rows are of text A. If all the rows are A change the Status# in the Tmaster table to R if not leave the Status# as it was.

I hope this is clear and let me know if you have any questions.

Thanks in advance for your help.

P

tmaster and tdetail.pbix (81.0 KB)
Tdetail and Tmaster.xlsx (11.5 KB)

2

Hi,

I tried to solve your problem and i am not sure about what do you mean by

So i assume if the lineitem conation same type of “A” then your staus colum value else it give R.

Anurag_Status =

Var tb=FILTER(Tdetail,Tdetail[Employee]=Tmaster[Employee] && Tdetail[EndDate]=Tmaster[EndDate])

Var lineapp=CONCATENATEX(tb,Tdetail[LineItemApprovalStatus])

return

if(CONTAINSSTRING(lineapp,“A”),Tmaster[Status #],“R”)

Attaching the sol pbix let me know is this what you want to achive:
tmaster and tdetail.pbix (97.8 KB)

4

I am not sure this is exactly what I am after and I don’t think this DAX quite works. Thanks for the try Anurag. I am more focused in getting a solution via power query m code. Appreciate your help though.

5

Hi,

if you can share the expected result i will try to come with a sol in M code which can be helpful for me as well for other expert in the form.

Thanks,
Anurag

6

If you look at this screenshot and focus on Employee2 in Tmaster table. I basically want to check that this employee2 and its Enddate matches the employee2 and enddate in the Tdetail table once that is the case I want it to scan the Lineitemapprovalstatus column in Tdetail and they all of have to be A. If they are all A then I want to create a new status column in Tmaster change its old status# A to R. If you see Employee1 in Tmaster table its Newstatus is still A because not all of its rows in Tdetail have all change to A. I hope that is more clear

image
image1003×442 41.1 KB

7

HI @Melissa ,

Hope you well. I am not sure if you had a chance to look at this for me please?

Thanks

P

8

Hi @ambepat,

See if this meets your requirement. This updates the [Status #] column conditionally.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNTTUNTJU0lEyMIczDY3MDUxBDNfcgpz8ytRUENsRiEO9QbLmugZmuoaWSrE6eA0wQjLACLsBsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WeekCommencing = _t, EndDate = _t, Employee = _t, #"Employee Name" = _t, #"Status #" = _t, HomeCountry = _t, HireDate = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"WeekCommencing", type date}, {"EndDate", type date}, {"Employee", Int64.Type}, {"Employee Name", type text}, {"Status #", type text}, {"HomeCountry", type text}, {"HireDate", type date}}),
    MergeTdetail = Table.NestedJoin( ChType, {"EndDate", "Employee"}, Tdetail, {"EndDate", "Employee"}, "tTemp", JoinKind.LeftOuter),
    UpdateStatus = 
        Table.RemoveColumns( 
            Table.ReplaceValue( MergeTdetail, 
                each [#"Status #"], 
                each if List.MatchesAll( [tTemp][LineItemApprovalStatus], each _ = "A") then "R" else [#"Status #"], 
                Replacer.ReplaceText, 
                {"Status #"} 
            ), "tTemp" 
        )
in
    UpdateStatus

.
With this result. Please note that I had to update the supplied xlsx sample to match your screenshot in both Employee and Status # columns.

image
image1157×74 8.84 KB

Here’s your sample file.
tmaster and tdetail.pbix (55.0 KB)

I hope this is helpful.

9

Thanks @Melissa

10

Thank you so much for that detailed answer @Melissa

We hope this helped you @ambepat :slight_smile:

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.