M Novice Question

[Anonymous]

So I am a novice at M. I can get around Power Query for my every day but now I am trying to handle more complex issues and feel I need better info about M so that brought me here. I have already looked at some resources presented and that is a great help so thanks for those. 

My question/problem currently is this: 

From each row, in my data set, I have to figure out which team members are not listed. This is the first issue

Next, I have to make sure that each team member not listed was on the team for the date listed on the row.

• So far I have my main data table which has 6 columns that I have to check for names. 
• I have a second table that has a list of all team members with both a start and term date
• I need to be able to identify who is not on the first table row and if the transaction date for the row on the first data table falls within the employment period that is listed for each non-participant on the second table. 
• From there I will need to output to columns on the first table or another separate table with a list that shows the name and in addition, it will show a $ Split amount for that person. 

The real-world explanation: This is for a Broker Split output where we have multiple Brokers on the team that are not involved in the deal but still receive a small % split of the fee. As an example you have a $100K Fee and Brokers not involved get a 1% split ($1000) and there are 4 Brokers on the team that were not involved they each get $250. This is why I have to find brokers not involved and also have to make sure they were employed at the time of the deal. 

Hopefully this is not clear as mud!

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+xDoMwDER/JYoYI2xf4lJ21m7dEEOHbvz/XKo4ckBMZ1u5p5d1jcv3sweJKb5fLfIR4XSBXQYkZh6Zj7kQlMDguCXDoL6eajxqqJNCw0jShlGS5wWTa6N0DVtNznBDdpuZhC+YcovJvQfcAyT/vnhfT98QR6BHFHeYSNQY2w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Deal Name" = _t, #"Seller AIC 1" = _t, #"Seller AIC 2" = _t, #"Seller AIC 3" = _t, #"Buyer AIC 1" = _t, #"Buyer AIC 2" = _t, #"Buyer AIC 3" = _t, Split = _t, #"Transaction Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Deal Name", type text}, {"Seller AIC 1", type text}, {"Seller AIC 2", type text}, {"Seller AIC 3", type text}, {"Buyer AIC 1", type text}, {"Buyer AIC 2", type text}, {"Buyer AIC 3", type text}, {"Split", Currency.Type}, {"Transaction Date", type date}}),




    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Team Member", each #"Table (2)"[Team Member]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "NotInvolved", each List.RemoveItems(
    [Team Member],
    {[Seller AIC 1],[Seller AIC 2],[Seller AIC 3],[Buyer AIC 1],[Buyer AIC 2],[Buyer AIC 3]}
)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "NotInvolvedTable", 
(r)=>
Table.SelectRows(#"Table (2)", (x)=>List.Contains(r[NotInvolved],x[Team Member])
)

),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "NotInvolvedTable_isIN", 
(r)=>
Table.SelectRows(#"Table (2)", (x)=>List.Contains(r[NotInvolved],x[Team Member])
and (
(r[Transaction Date]>=x[Start Date] and r[Transaction Date]<=x[End Date]) or (r[Transaction Date]>=x[Start Date] and x[End Date] is null)
)
)

),
    #"Expanded NotInvolvedTable_isIN" = Table.ExpandTableColumn(#"Added Custom3", "NotInvolvedTable_isIN", {"Team Member", "Start Date", "End Date"}, {"NotInvolvedTable_isIN.Team Member", "NotInvolvedTable_isIN.Start Date", "NotInvolvedTable_isIN.End Date"})
in
    #"Expanded NotInvolvedTable_isIN"

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Zeon,

Thank you for taking a look at this and putting something together. I will take a look and see if that gets me what I need. 

[Anonymous]

Good Suggestion Vijay. Sorry I didn't think about that. 

Hopefully, this will work.

First Table mimics the table I am working on.

Deal Name	Seller AIC 1	Seller AIC 2	Seller AIC 3	Buyer AIC 1	Buyer AIC 2	Buyer AIC 3	Split	Transaction Date
Deal 1	TM 1	TM 3		TM 1	TM 2		$2,000.00	4/25/2020
Deal 2	TM 7	TM 6	TM 5	TM 1			$1,500.00	5/18/2020
Deal 3	TM 4			TM 4	TM 3	TM 1	$3,000.00	9/10/2020
Deal 4	TM 4			TM3			$2,500.00	2/1/2021
Deal 5	TM 6	TM 1		TM 2			$4,000.00	7/15/2021

The second table is what I want to check against

Team Member	Start Date	End Date
TM 1	1/1/2019
TM 2	2/6/2019	6/2/2021
TM 3	6/10/2019
TM 4	6/10/2019
TM 5	1/15/2020	5/3/2021
TM 6	4/25/2020
TM 7	9/14/2020

[Vijay_A_Verma]

A screenshot or a sample file without confidential/sensitive data will be good to help you.