Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

M Novice Question

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!

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

4 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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. 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

    Hopefully, this will work.

    First Table mimics the table I am working on.

    Deal NameSeller AIC 1Seller AIC 2Seller AIC 3Buyer AIC 1Buyer AIC 2Buyer AIC 3SplitTransaction Date
    Deal 1TM 1TM 3 TM 1TM 2 $2,000.004/25/2020
    Deal 2TM 7TM 6TM 5TM 1  $1,500.005/18/2020
    Deal 3TM 4  TM 4TM 3TM 1$3,000.009/10/2020
    Deal 4TM 4  TM3  $2,500.002/1/2021
    Deal 5TM 6TM 1 TM 2  $4,000.007/15/2021

     

    The second table is what I want to check against

    Team MemberStart DateEnd Date
    TM 11/1/2019 
    TM 22/6/20196/2/2021
    TM 36/10/2019 
    TM 46/10/2019 
    TM 51/15/20205/3/2021
    TM 64/25/2020 
    TM 79/14/2020