cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ibutler
Regular 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
nsrshkh1
Resolver IV
Resolver IV

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

 

View solution in original post

1 REPLY 1
nsrshkh1
Resolver IV
Resolver IV

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

 

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors