Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Stubrg
Helper I
Helper I

Add a Lead time to date dependent on stock qty

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

2 ACCEPTED SOLUTIONS

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

ronrsnfld_0-1680121916142.png

with added column

ronrsnfld_1-1680121949303.png

 

 

 

View solution in original post

Many Thanks that works brilliantly!!

View solution in original post

4 REPLIES 4
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete

Thanks for responding,

 

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

BeforeBefore

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

 

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

ronrsnfld_0-1680121916142.png

with added column

ronrsnfld_1-1680121949303.png

 

 

 

Many Thanks that works brilliantly!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors