Forum Discussion

Mars3442's avatar
Mars3442
Helper I
2 years ago

Replace value that between 2 date

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

 

Need Help

3 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    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's avatar
      Mars3442
      Helper I
      2 years ago

      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's avatar
        ronrosenfeld
        Frequent Visitor
        2 years ago

        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