cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ibutler Visitor
Visitor

Expand a table with field values to create composite key

Hi All,

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

Unique NumberCustomer NumberStart DateEnd Date
1000011/01/20192/01/2019
1000123/03/20193/03/2019
10002110/01/201920/01/2019
1000338/01/20199/01/2019
1000417/01/20199/01/2019

Table 2 contains data like:

Unique NumberCustomer NumberDate
x112/01/2019
x223/03/2019
x3110/01/2019
x438/01/2019
x517/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/2019Matched1-02/01/2019
2-03/03/2019Matched2-03/03/2019
1-10/01/2019Matched1-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/2019Matched3-08/01/2019
3-09/01/2019  
1-07/01/2019Matched1-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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
nsrshkh1 Member
Member

Re: Expand a table with field values to create composite key

@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.

 

1 REPLY 1
Highlighted
nsrshkh1 Member
Member

Re: Expand a table with field values to create composite key

@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.

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 32 members 829 guests
Please welcome our newest community members: