Expand a table with field values to create composite key

[ibutler]

Hi All,

I need to join two tables making a composite key, Table 1 contains data like:

Unique Number	Customer Number	Start Date	End Date
10000	1	1/01/2019	2/01/2019
10001	2	3/03/2019	3/03/2019
10002	1	10/01/2019	20/01/2019
10003	3	8/01/2019	9/01/2019
10004	1	7/01/2019	9/01/2019

Table 2 contains data like:

Unique Number	Customer Number	Date
x1	1	2/01/2019
x2	2	3/03/2019
x3	1	10/01/2019
x4	3	8/01/2019
x5	1	7/01/2019

As you can tell there is no primary key that will directly link the fields (the customer number is identical in both tables but the dates vary), what I need to achieve is matching the records from table 2 with table 1 where the customer number and date from table 2  (composite key) occurred during the period between start date  & end date. My thought was to create an additional table that extracts all possible dates between the occurances in table 1's start date & end date at a customer level. The result table would look like the following:

Table 3 (Based on Table 1)		Table 4 (Based on Table 2)
Composite Key		Composite Key
1-01/01/2019
1-02/01/2019	Matched	1-02/01/2019
2-03/03/2019	Matched	2-03/03/2019
1-10/01/2019	Matched	1-10/01/2019
1-11/01/2019
1-12/01/2019
1-13/01/2019
1-14/01/2019
1-15/01/2019
1-16/01/2019
1-17/01/2019
1-18/01/2019
1-19/01/2019
1-20/01/2019
3-08/01/2019	Matched	3-08/01/2019
3-09/01/2019
1-07/01/2019	Matched	1-07/01/2019
1-08/01/2019
1-09/01/2019

Then filter out the nulls. Any thoughts or better ways to achieve this?

Thanks

[Anonymous · Accepted solution]

@ibutler ,

Your method is correct. Just instead of creating all combinations of dates, you can create only those combinations for which there is Start date or End date (Assuminng Table2 has same dates as Table1 either in Start or End).

Table1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAiUdJUMQ1jfUNzIwtAQyjWDMWB2IGpC8ERAb6xvD1MCZMDVGMHMMkAwy0DdAM8oYpBeILRCqLNGtM4EaZY5NTSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Unique Number" = _t, #"Customer Number" = _t, #"Start Date" = _t, #"End Date" = _t]),
    ChangedTypewithLocale = Table.TransformColumnTypes(Source, {{"Unique Number", Int64.Type}, {"Customer Number", Int64.Type}, {"End Date", type date},{"Start Date", type date}}, "en-IN"),
    UnpivotedColumns = Table.UnpivotOtherColumns(ChangedTypewithLocale, {"Unique Number", "Customer Number"}, "Attribute", "Date"),
    RemovedColumns = Table.RemoveColumns(UnpivotedColumns,{"Attribute"}),
    RemovedDuplicates = Table.Distinct(RemovedColumns),
    MergedQueries = Table.NestedJoin(RemovedDuplicates, {"Customer Number", "Date"}, Table2, {"Customer Number", "Date"}, "Table2", JoinKind.LeftOuter),
    ExpandedTable2 = Table.ExpandTableColumn(MergedQueries, "Table2", {"Unique Number"}, {"Unique Number.1"})
in
    ExpandedTable2

Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqjBU0lECYSN9Q30jA0NLpVgdoKgRSASIjfWNkUSNoWoNDVAUm4AUArEFiqgpVLE5XDQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Unique Number" = _t, #"Customer Number" = _t, Date = _t]),
    ChangedTypewithLocale = Table.TransformColumnTypes(Source, {{"Unique Number", type text}, {"Customer Number", Int64.Type}, {"Date", type date}}, "en-IN"),
    RemovedDuplicates = Table.Distinct(ChangedTypewithLocale)
in
    RemovedDuplicates

If dates are random then your solution is best.

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