Create a new table from two existing tables based on conditions

[davidmc1]

Hi Community,

I'm trying to create a new table using info from two existing tables that have a many to many relationship based on the 'User' field below.  The two tables are table 1 and table 2 below.  Basically a user can have multiple entries on the same day in both tables. 

What I want to do is create a new table that only selects the INC numbers from Table 2 that fall between the inter_opened_at and Inter_opened_at+24h dates from table 1, listed per user.  The solution I would like can be seen in excel form in table 3.  For example, User 3 has opened 3 INC records but only 1 meets the criteria and therefore is counted.  I'm running into errors trying to relate the tables due to the many to many relationship.

Stuck on how I can make this work so any help would be appreciated!  Thank you.

[jgeddes · Accepted solution]

You can replicate what you did in Excel using Power Query. 

Merge table 1 into table 2, expand the columns from table 1, create a condition column, filter the condition column, remove the condition column, re-order the columns to match your needs.

Here is a sample code you can paste into the advanced editor of a blank query.

let
    #"Table 1" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJRstA31TcyMDIBM81gTE/fYEMDSzNjA0ulWB2oaiNUJRb65qiqLQwNzRGqjQmotjQ2M1WKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, inter_opened_at = _t, #"Inter_opened_at_+24h" = _t, inter_number = _t]),
    #"T1Changed Type" = Table.TransformColumnTypes(#"Table 1",{{"User", type text}, {"inter_opened_at", type date}, {"Inter_opened_at_+24h", type date}, {"inter_number", type text}}),      
    #"Table 2" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc+7DkIxCAbgVzHMJ2mhXGcnFzenk46+gL5/IpulDm5AvvDDecLj/XxdEA6wZo06cZa3+3WEsDjDPP4QqcSbLIT7YCbciVbiavEllHNtsQa5htNGfrYEO1YSleBwGwsZOc9jsSRhN9Vq9iTOZlsTDXuJknzcYc4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, opened_at = _t, number = _t]),
    #"T2Changed Type" = Table.TransformColumnTypes(#"Table 2",{{"User", type text}, {"opened_at", type date}, {"number", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"T2Changed Type", {"User"}, #"T1Changed Type", {"User"}, "Table 1", JoinKind.LeftOuter),
    #"Expanded Table 1" = Table.ExpandTableColumn(#"Merged Queries", "Table 1", {"inter_opened_at", "Inter_opened_at_+24h", "inter_number"}, {"inter_opened_at", "Inter_opened_at_+24h", "inter_number"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table 1", "condition met", each if [opened_at] >= [inter_opened_at] and [opened_at] <= [#"Inter_opened_at_+24h"] then true else false, type logical),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([condition met] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"condition met"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"User", "inter_opened_at", "Inter_opened_at_+24h", "inter_number", "number", "opened_at"})
in
    #"Reordered Columns"

Using your sample data you should end up with...

You may need to adjust the logic in the condition column to match your needs.

Hope this gets you pointed in the right direction.

[dufoq3 · Accepted solution]

Hi @davidmc1, another solution here:

just keep in mind that in your examle there are 2 matches for User 1

Output

let
    T1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJRMtWz0DMyMDIBMg3M9A0s9KEcT99gT0MDSzNjA0ulWB2oBiOghBmSBnNMDRaGhuYIDcZ4NBj6BgPVWxqbmSrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, #" inter_opened_at" = _t, #"Inter_opened_at+24h" = _t, inter_number = _t]),
    T2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdCxCgMxCAbgVynOB1Gjic6dbunW6ch4L3B9f6idapOh4KJ8+IvHAc/Xed0INsBeohhZotkf9+oqagJj+4t0QlrQEhKsIkwTaguy1v2L+IO8hEtx1tx4RusmF6MVeUZUrdeEasyZStye4wh7a79ojZNopk2Ec5zGEwzGeAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Opened_at = _t, number = _t]),
    T1_ChangedType = Table.TransformColumnTypes(T1,{{" inter_opened_at", type date}, {"Inter_opened_at+24h", type date}}),
    T2_ChangedType = Table.TransformColumnTypes(T2,{{"Opened_at", type date}}),
    T1_Ad_Dates = Table.AddColumn(T1_ChangedType, "Date", each List.Dates([#" inter_opened_at"], Duration.TotalDays([#"Inter_opened_at+24h"]-[#" inter_opened_at"])+1, #duration(1,0,0,0)), type {date}),
    T1_Ad_Index = Table.AddIndexColumn(T1_Ad_Dates, "Index", 0, 1, Int64.Type),
    T1_ExpandedDate = Table.ExpandListColumn(T1_Ad_Index, "Date"),
    MergedQueries = Table.NestedJoin(T1_ExpandedDate, {"User", "Date"}, T2_ChangedType, {"User", "Opened_at"}, "T2", JoinKind.LeftOuter),
    AddedIndex = Table.AddIndexColumn(MergedQueries, "Index.1", 0, 1, Int64.Type),
    ExpandedT2 = Table.ExpandTableColumn(AddedIndex, "T2", {"Opened_at", "number"}, {"Opened_at", "number"}),
    GroupedRows = Table.Group(ExpandedT2, {"User", "Index", "Opened_at"}, {{"All", each _}, {"T", each [
        a = Table.FirstN(Table.RemoveColumns(_,{"Date", "Index", "number", "Index.1"}), 1),
        b = Table.FromColumns(Table.ToColumns(a) & {{Text.Combine([number], ", ")}}, Value.Type(Table.FirstN(a, 0) & #table(type table[number=text], {})))
      ][b], type table }}, 0),
    FilteredRows = Table.SelectRows(GroupedRows, each ([Opened_at] <> null)),
    T = Table.Combine(FilteredRows[T])
in
    T

[jgeddes · Accepted solution]

You can replicate what you did in Excel using Power Query. 

Merge table 1 into table 2, expand the columns from table 1, create a condition column, filter the condition column, remove the condition column, re-order the columns to match your needs.

Here is a sample code you can paste into the advanced editor of a blank query.

let
    #"Table 1" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJRstA31TcyMDIBM81gTE/fYEMDSzNjA0ulWB2oaiNUJRb65qiqLQwNzRGqjQmotjQ2M1WKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, inter_opened_at = _t, #"Inter_opened_at_+24h" = _t, inter_number = _t]),
    #"T1Changed Type" = Table.TransformColumnTypes(#"Table 1",{{"User", type text}, {"inter_opened_at", type date}, {"Inter_opened_at_+24h", type date}, {"inter_number", type text}}),      
    #"Table 2" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc+7DkIxCAbgVzHMJ2mhXGcnFzenk46+gL5/IpulDm5AvvDDecLj/XxdEA6wZo06cZa3+3WEsDjDPP4QqcSbLIT7YCbciVbiavEllHNtsQa5htNGfrYEO1YSleBwGwsZOc9jsSRhN9Vq9iTOZlsTDXuJknzcYc4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, opened_at = _t, number = _t]),
    #"T2Changed Type" = Table.TransformColumnTypes(#"Table 2",{{"User", type text}, {"opened_at", type date}, {"number", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"T2Changed Type", {"User"}, #"T1Changed Type", {"User"}, "Table 1", JoinKind.LeftOuter),
    #"Expanded Table 1" = Table.ExpandTableColumn(#"Merged Queries", "Table 1", {"inter_opened_at", "Inter_opened_at_+24h", "inter_number"}, {"inter_opened_at", "Inter_opened_at_+24h", "inter_number"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table 1", "condition met", each if [opened_at] >= [inter_opened_at] and [opened_at] <= [#"Inter_opened_at_+24h"] then true else false, type logical),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([condition met] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"condition met"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"User", "inter_opened_at", "Inter_opened_at_+24h", "inter_number", "number", "opened_at"})
in
    #"Reordered Columns"

Using your sample data you should end up with...

You may need to adjust the logic in the condition column to match your needs.

Hope this gets you pointed in the right direction.