Two fact tables: with DATE and START_DATE & END_DATE columns - how to merge them?

[jagnejszyn]

Hi everyone!

I have two fact tables with the sales data and I would like to merge and get rid of one of them.

The structure looks as follows:
FACT_1: dim1, dim2, dim3, dim4, sales_date, price1

FACT_2: dim1, dim2, dim3, start_date, end_date, price2

I'd like to create an additional column in FACT_1 table (prefarably in PQ but not sure if it's possible, so a solution for the DAX calculation column would also be acceptable) to get the price2 value from the FACT_2 table for each row, where sales_date is between the start_date and end_date (in the FACT_2 table there are more that one row for each combination of dim1, dim2 & dim3, with a different time range and price2 value).

Having the price2 column in the FACT_1 table would be incredibly helpful because in some of the measures I have to iterate through the FACT_1 table - I could then refer directly to the value of the column created for each row.

Any solution/idea highly appreciated! 🙂

[sevenhills · Accepted solution]

Not sure whether it is a good idea to bring different level of facts into one. It is a common scenario of type 2 dim data joining to fact i.e., yours like more as merge join on range of dates.

Let us do this and see if it works for your needs.

a) Assuming your fact2 data is matching one for every join else you may have to add some more logic.

b) Adjust your >= and <= if needed as some use > or< in the join M query

IP: Fact1

IP: Fact2

Output:

M Query:

Fact1 Table ... first two rows are your original source data. Rest is all I added to get the desired result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsnMNQxLzClNVdIBsY2Q2MZIbBMY29BA1ysxT9fIwMgEzDNQitUhyxSX1GSQKcZAnqUluYY4FhTBnGJhgWqIEbIpRsjGGCGbY4ThJzMzSgzyTayEG2SuFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dim1 = _t, Dim2 = _t, Dim3 = _t, Dim4 = _t, Sales_Date = _t, Price1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dim1", type text}, {"Dim2", type text}, {"Dim3", type text}, {"Dim4", type text}, {"Sales_Date", type date}, {"Price1", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Dim1", "Dim2", "Dim3"}, Fact2, {"Dim1", "Dim2", "Dim3"}, "Fact2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each let tt_Date = [Sales_Date] in Table.SelectRows([Fact2], each [Start_Date] <= tt_Date and tt_Date <= [End_Date])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Price2"}, {"Price2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Price2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Fact2"})
in
    #"Removed Columns"

Tip: You can use Table.Buffer() if there is a performance issue before #"Added Custom"

Hope this helps!

[jagnejszyn]

It worked, thank you so much! You're my hero! 😎

[sevenhills]

Glad to hear it helped. 🙂