Forum Discussion

OzMartijn's avatar
OzMartijn
Frequent Visitor
2 years ago

Show records as at a certain date

Hi all,   I have a conundrum. The conundrum being multiple tables connected via PID (primary identifier). All tables have a record start date and a record end date (end date is blank if it's a curr...
Need Help
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi OzMartijn, regarding this is PQ forum here is PQ solution (but it will be too slow for 18M rows of data).

You can filter via [Date] column and see the Value and Location for exact date.

 

Result

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABHG+oYG+kYGRsZAjpEZnBOrA1VkChI3R1JkaKlvYAzimCAUmYMUGcDFdZQUwHJGIDkjdFsgcmCjTNDlDIE2GUJdEAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PID = _t, Value = _t, Start_date = _t, End_date = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXI3AJGGxvqGBvpGBkbGQI6Rsb6BEYhjohSrA1HlZmAOkjCBS+goKYAljYAsJwMLdCOAkrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PID = _t, Location = _t, Start_date = _t, End_date = _t]),
    T1_ChangedType = Table.TransformColumnTypes(Table1,{{"PID", Int64.Type}, {"Value", Int64.Type}, {"Start_date", type date}, {"End_date", type date}}),
    T1_Ad_Date = Table.AddColumn(T1_ChangedType, "Date", each List.Dates([Start_date], Duration.TotalDays((if [End_date] = null then Date.From(DateTime.FixedLocalNow()) else [End_date]) - [Start_date])+1, #duration(1,0,0,0)), type list),
    T1_ExpandedDate = Table.ExpandListColumn(T1_Ad_Date, "Date"),
    T2_ChangedType = Table.TransformColumnTypes(Table2,{{"PID", Int64.Type}, {"Start_date", type date}, {"End_date", type date}}, "sk-SK"),
    T2_Ad_Date = Table.AddColumn(T2_ChangedType, "Date", each List.Dates([Start_date], Duration.TotalDays((if [End_date] = null then Date.From(DateTime.FixedLocalNow()) else [End_date]) - [Start_date])+1, #duration(1,0,0,0)), type list),
    T2_ExpandedDate = Table.ExpandListColumn(T2_Ad_Date, "Date"),
    MergedQueries = Table.NestedJoin(T1_ExpandedDate, {"PID", "Date"}, T2_ExpandedDate, {"PID", "Date"}, "T2_ExpandedDateList", JoinKind.LeftOuter),
    ExpandedT2_Location = Table.ExpandTableColumn(MergedQueries, "T2_ExpandedDateList", {"Location"}, {"Location"}),
    RemovedColumns = Table.RemoveColumns(ExpandedT2_Location,{"Start_date", "End_date"}),
    ChangedType = Table.TransformColumnTypes(RemovedColumns,{{"Date", type date}})
in
    ChangedType

 

ManuelBolz's avatar
ManuelBolz
Responsive Resident
2 years ago

Hello OzMartijn 

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

Thank you for your interesting problem. Here is my solution approach.


Table 1:
Your PID/Value Table: testTable1


Table 2:
Your PID/Location Table: testTable2

 

Custom Function: 
fnGetLocationValue

let
    Source1 = testTable1,
    Source2 = testTable2,
    
    Table1Updated = Table.TransformColumns(Source1, {{"End_date", each if _ = null then DateTime.Date(DateTime.LocalNow()) else _, type date}}),
    Table2Updated = Table.TransformColumns(Source2, {{"End_date", each if _ = null then DateTime.Date(DateTime.LocalNow()) else _, type date}}),
    
    CustomFunction = (inputPID as number, inputDate as date) =>
        let
            FilteredTable1 = Table.SelectRows(Table1Updated, each [PID] = inputPID and [Start_date] <= inputDate and [End_date] >= inputDate),
            FilteredTable2 = Table.SelectRows(Table2Updated, each [PID] = inputPID and [Start_date] <= inputDate and [End_date] >= inputDate),
            MatchedRows = Table.SelectRows(FilteredTable2, each List.NonNullCount(
                List.Transform(FilteredTable1[Start_date], (start1) => [Start_date] <= inputDate and [End_date] >= inputDate)
            ) > 0),
            Result = if Table.RowCount(FilteredTable1) > 0 and Table.RowCount(MatchedRows) > 0 then
                [Value = FilteredTable1{0}[Value], Location = MatchedRows{0}[Location]]
            else
                null
        in
            Result
in
    CustomFunction


Result Table:
You can call up the function here

    fnGetLocationValue = Table.AddColumn(Source, "fnGetLocationValue", each fnGetLocationValue([PID],[Date])),

 



Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github