Add a Lead time to date dependent on stock qty

[Stubrg]

Hi I am trying to add product lead times to required dates from 2 seperate columns based on qtys of stock in another column,

i have tried using an if statement as below, this query is on a large data set:

 = Table.AddColumn(#"Sorted Rows1", "ORDER DATE", each if [Forecast qty Covered by Stock] <= 0 then Table.AddColumn(#"Reordered Columns", "Order By Date", each Date.AddDays([Details.Date required],[#"Details.Lead Time (Days)"])) else null)

This is creating indivual tables in each cell of the Column which I am having to expand out and select the "ORDER DATE" column. Any help would be appreciated.

This is working in the query, but when i am closing and loading the query it is taking hours!! to refresh. Is there a way of speeding this up? not sure how to use Buffer function on this??

[ronrsnfld · Accepted solution]

You have "nested" the Table.AddColumn function which is why you are getting a column of tables.

Try this (my table source is in Excel), using the example you provided, so the column names are different:

let
    Source = Excel.CurrentWorkbook(){[Name="Stock"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date required", type date}, {"Part", type text}, {"Lead Time (Days)", Int64.Type}, {"Order qty", Int64.Type}, {"Stock", Int64.Type}, {"Stock2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Order by", each 
        if [Stock] <= 0
             then Date.AddDays([Date required],-[#"Lead Time (Days)"])
              else null, type date)
in
    #"Added Custom"

Original

with added column

[BA_Pete]

Hi @Stubrg ,

Can you provide a simple example of what your data looks like at the start, and another example of how you expect it to look at the end please?

You're adding another Table.AddColumn function into each new column calculation which will likely be causing your performance issues, but I can't be sure if that's intentional or not without understanding your before/after positions.

Pete

[Stubrg]

Hi Pete

Thanks for responding,

Below is an example of what the report currently looks like:

Before

I am trying to add a new column that shows the order by date, which is calculated from the "Date Required" column subtracting the "Lead time in (days)" to alert the storeman to reorder. Sorry for posting that i was attempting to add these together, I meant to subtract.

But I only want to carry out this function if the "Stock" is showing 0 or less. Then the "Order by Date" is shown.

How I would like the report to look after Power query calc.

I appreciate any help you can offer as I am pretty new to Power Query, I can acheive this in Excel. But would like Power query to action this for me.  The data set in reality is approx 2500 lines.

[ronrsnfld · Accepted solution]

You have "nested" the Table.AddColumn function which is why you are getting a column of tables.

Try this (my table source is in Excel), using the example you provided, so the column names are different:

let
    Source = Excel.CurrentWorkbook(){[Name="Stock"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date required", type date}, {"Part", type text}, {"Lead Time (Days)", Int64.Type}, {"Order qty", Int64.Type}, {"Stock", Int64.Type}, {"Stock2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Order by", each 
        if [Stock] <= 0
             then Date.AddDays([Date required],-[#"Lead Time (Days)"])
              else null, type date)
in
    #"Added Custom"

Original

with added column

[Stubrg · Accepted solution]

Many Thanks that works brilliantly!!