Create From date & End Date colums base on Date columns event

[ngocrin1]

Hi everyone,

I need transform by Power Query with this orignal data (Tbl 1) to new table (tbl 2) and use it to lookup Code column base on Order Date (tbl 3) 

                    1                                                                    2                                                                                  3

 Has everyone help me to solve this problem?

Thank you.

[ngocrin1]

Tks @aduguid but I need PQ than by Dax.
@Anonymous  it work but with the fact table (Order) with 100k rows it refresh quite long.

[aduguid]

First create a ranking for the "code"

Code = 
RANKX(
    FILTER(
        'ItemsTable',
        'ItemsTable'[Items] = EARLIER('ItemsTable'[Items])
    ),
    'ItemsTable'[Date],
    ,
    ASC,
    DENSE
)

Then create a calculated column for the next date in the series

To Date = 
VAR CurrentItem = 'ItemsTable'[Items]
VAR CurrentDate = 'ItemsTable'[Date]
RETURN
    CALCULATE(
        MIN('ItemsTable'[Date]) - 1,
        FILTER(
            'ItemsTable',
            'ItemsTable'[Items] = CurrentItem &&
            'ItemsTable'[Date] > CurrentDate
        )
    )

[Anonymous · Accepted solution]

Hi @ngocrin1 ,

About Step1(Tbl 1 > Tbl 2):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUTLUN9Q3MjAyUYrVgYuZ6hthiJmhqTPCotcIRW8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Items", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Items"}, {{"Count", each Table.AddIndexColumn(_, "Code", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Code"}, {"Date", "Code"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Count",{"Items", "Code", "Date"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each let
        currentItems = [Items],
        currentCodePlusOne = [Code] + 1,
        relatedRow = Table.SelectRows(#"Reordered Columns", each [Items] = currentItems and [Code] = currentCodePlusOne)
    in
        if Table.IsEmpty(relatedRow) 
        then null 
        else Date.AddDays(relatedRow{0}[Date], -1)),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Date", "From"}, {"Custom", "To"}})
in
    #"Renamed Columns"

Then about Step2(Tbl 2 > Tbl 3):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUTLSN9Q3MjAyUYrVgYuZ6ptjiBka6ZuiCBoBBY31TTDEzOCaYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, OrderDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Items", type text}, {"OrderDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
    OrderDate = [OrderDate],
    _items=[Items],
    MatchRow = Table.SelectRows(Table, each [Items] = _items and [From] <= OrderDate and [To] >= OrderDate),
    MaxCode=List.Max(Table.SelectRows(Table,each [Items]=_items)[Code]),
    Result = try MatchRow{0}[Code] otherwise null
in
    if Result<>null then Result else MaxCode)
in
    #"Added Custom"

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