Replace value that between 2 date

[Mars3442]

Hi everyone,

I want to ask for help solving my problem

I have a note of available products like this

The availability of the product can be created new date or can be extended

My raw data like this

I need to replace the "error" status with these conditional :

If the order occurs between the data availability date = replace the error with "Create Memo"

If the order does not occur between the data availability date = don't replace the status

Without replacing the cell that have been filled

My expectation table like this

Colored cells mean they are available in notes.

Cells are colored based on availability date.

However, if not, it means the product is not on the availability date note

What M Code should I write to get my expectations?

Please help me to solve this problem

Thank you..

[ronrsnfld]

Here's one way. The algorithm is in the code comments:

let

//Read in the Availability Table
    Source = Excel.CurrentWorkbook(){[Name="Availablity"]}[Content],
    Availability = Table.TransformColumnTypes(Source,{
        {"Product Name", type text}, {"Start Date", type date}, {"End Date", type date}}),

//Group by Product
//then create a list of all available dates for each product
    #"Grouped Rows" = Table.Group(Availability, {"Product Name"}, {
        {"Available", (t)=> 
            List.Combine(
                List.Generate(
                    ()=>[d=List.Dates(
                            t[Start Date]{0}, 
                            Duration.Days(t[End Date]{0} - t[Start Date]{0})+1, 
                            #duration(1,0,0,0)),
                         idx = 0],
                    each [idx] < Table.RowCount(t),
                    each [d=List.Dates(
                            t[Start Date]{[idx]+1}, 
                            Duration.Days(t[End Date]{[idx]+1} - t[Start Date]{[idx]+1})+1, 
                            #duration(1,0,0,0)),
                        idx = [idx]+1],
                    each [d]
            ))
        }}), 
        
//Read in the "raw" table
    Source2 = Excel.CurrentWorkbook(){[Name="raw"]}[Content],
    raw = Table.TransformColumnTypes(Source2,
        List.Zip({
            {"Store","Product","Order Date","Status"}, 
            {type text, type text, type date, type nullable text}}
        )),

//Join with the Available table and extract the List of Available dates for each product
    #"Join Available" = Table.NestedJoin(raw,"Product", #"Grouped Rows","Product Name","Available",JoinKind.LeftOuter),
    #"Available List" = Table.TransformColumns(#"Join Available", {"Available", each List.Combine([Available])}),

//Replace "Error" with "Create Memo" based on your logic
    #"Replace Error" = Table.ReplaceValue(
        #"Available List",
        each [Status],
        each if [Status] = "Error" and List.Contains([Available],[Order Date]) then "Create Memo" else [Status],
        Replacer.ReplaceValue,
        {"Status"}
    ),
    
//Cleanup
    #"Removed Columns" = Table.RemoveColumns(#"Replace Error",{"Available"}),
    #"Type Status" = Table.TransformColumnTypes(#"Removed Columns",{"Status", type text})
in
    #"Type Status"

[Mars3442]

Thank you for your help

But can you explain this part?

//Group by Product
//then create a list of all available dates for each product
    #"Grouped Rows" = Table.Group(Availability, {"Product Name"}, {
        {"Available", (t)=> 
            List.Combine(
                List.Generate(
                    ()=>[d=List.Dates(
                            t[Start Date]{0}, 
                            Duration.Days(t[End Date]{0} - t[Start Date]{0})+1, 
                            #duration(1,0,0,0)),
                         idx = 0],
                    each [idx] < Table.RowCount(t),
                    each [d=List.Dates(
                            t[Start Date]{[idx]+1}, 
                            Duration.Days(t[End Date]{[idx]+1} - t[Start Date]{[idx]+1})+1, 
                            #duration(1,0,0,0)),
                        idx = [idx]+1],
                    each [d]
            ))
        }}), 

[ronrosenfeld]

What about that code do you not understand? Examine the applied step. You will see it creates a List of available dates for each product. Later on we will use that List to determine if the order occurs at a time when that product is available