Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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..
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"
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] )) }}),
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