Combine two Tables (Actual and Forecast) into third table

[Anonymous]

I have what seems to me like a complicated problem in Power BI and just want to see if this is possible because going out to look for another solution. I'm trying to combine information from two different tables into one table to show the current production path of a product with the forecasted pathway appended to the end. 

My actual results updated each day showing which production line was used and what the start and end date are. The end date of the final line is always listed as today (See Table 1 below). The forecast just shows the planned start and end dates for each line on the production path. 

What I'm looking to do is add the pathway from the forecast as additional rows in the Actuals data for the remaining days (Even if it's currently showing the product was somewhere other than the original forecasted plan -- We update the forecasts when we see divitations to create new forecasts). 

I was thinking I may need to use a union of some sort but wondered if someone has already done something similar. 

Thanks

Table 1 - Actuals: 

Name	Start Date	End Date	ID	Production Line
Product X	10/1/2018	10/7/2018	11	Line 1
Product X	10/7/2018	10/17/2018	11	Line 2
Product X	10/17/2018	10/19/2018	11	Line 4
Product X	10/19/2018	11/20/2018	11	Line 2
Product Y	10/6/2018	10/16/2018	22	Line 3
Product Y	10/16/2018	11/20/2018	22	Line 4

Table 2 - Forecast: 

Name	Start Date	End Date	ID	Production Line
Product X	10/1/2018	10/7/2018	11	Line 1
Product X	10/7/2018	10/17/2018	11	Line 2
Product X	10/17/2018	12/29/2018	11	Line 4
Product Y	10/6/2018	10/16/2018	22	Line 3
Product Y	10/16/2018	12/31/2018	22	Line 4

[Anonymous]

 Recently i worked on the similar data structure for the construction business. Take look at this steps:

Actual:

Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Name], Text.From([Start Date], "en-US"), Text.From([ID], "en-US"), [Production Line]}, ""), type text)

Forecast:

= Table.AddColumn(#"Renamed Columns", "Merged", each Text.Combine({[Name], Text.From([Start Date], "en-US"), Text.From([ID], "en-US"), [Production Line]}, ""), type text)

This way you can have a mutual calumn to create relationship between tables. I am assuming that only difference butween these tables is the End Date.

Please let me know how it goes for you.

Hasan

[Anonymous]

Thanks, but there are other differences. The production line could change between the actual and forecast, as we have reworks that could cause it to go back to another line for a bit and then jump back to the forecasted line. 

My goal right now is the just have the forecast append starting at the last day of the Actuals and then run through the end of the forecast. 

[Anonymous]

Here's how I tackled this.

1) Import both table into Power Query.  Added a Type column for each ( Actual or Forecast)

2) Used the append feature.  So  have a new table :

From there, create a merged column of ID, Production Line, Start Date, End Date.

Sorted those by that Type column (ascending, so we have actuals above Forecast)

Then deleted the duplicates from the Merged Column

Removed the merged column (which is one of the coolest things that you can do in Power Query as whenever the tables are uploaded it will still use that column).

Final table:

[Anonymous]

Hey Nick, 

Almost but not quite there, just appending and removing duplicates doesn't work because there is overlaping information that needs to be excluded. 

Example, Product X in the actuals was on Line 2 from 10/19 - 11/20. The forecast lists it as being on Line 4 from 10/17-10/29. I need that forecast to adjust to only consider information starting 11/20. So, it should go from Line 2 ending on 11/20 as shown in Actuals and then start Line 4 with a start date of 11/20 (Forecast). 

This is what is making this one so complicated. 

[Anonymous]

how does this look ? If good, ill type out what I did here

New Start Col:

[Anonymous · Accepted solution]

Looks good but I actually think I figured out a solution that produced the same table (Everything done in Power Query): 

1- I added the Type column to both tables (Actual or Forecast). 

2- In the forecast table I filtered the End Date to remove everything prior to today

= Table.SelectRows(#"Changed Type", each [End Date] >= Date.From(DateTime.LocalNow())  )

3- I removed the Start Date from the forecast and then added a custom Start Date column that is equal to Today's date:

= Table.AddColumn(#"Removed Columns", "Start Date", each DateTime.LocalNow())

4- Then I went back to the Actuals and appended the forecast. 

ID	Name	Production Line	Start Date	End Date	Source
11	Product X	Line 1	10/1/2018	10/7/2018	Actual
11	Product X	Line 2	10/7/2018	10/17/2018	Actual
11	Product X	Line 4	10/17/2018	10/19/2018	Actual
11	Product X	Line 2	10/19/2018	11/20/2018	Actual
22	Product Y	Line 3	10/6/2018	10/16/2018	Actual
22	Product Y	Line 4	10/16/2018	11/20/2018	Actual
11	Product X	Line 4	11/20/2018	12/29/2018	Forecast
22	Product Y	Line 4	11/20/2018	12/31/2018	Forecast

Thanks for the help. I am curious is you did it much differently but seems rather close as I see you created a separate start date. 

[Anonymous]

Still new to M, so using power query for this type of thing is still a little cumbersome.  But the more and more I do this I see how valuable it really is, as you demonstrated.  

Anyhow, here's how i accomplished it:

1. Add a Type ( Forecast or Actual) to each table
2. Append those two tables
3. Create a merged column of ID, Start Date, End Date
4. Sort the table first by Name and tehn by Type ( this will ensure Actual is before Forecast)
5. Remove duplicates from the merged column
6. Add a custom column Index on this table
7. Remove the Merged columns
8. Load this table into Power BI
   1. Create a new column called "New Start" using the index column from above, which would be the start date to use

   2. New Start = 
      VAR CurrentIndex = Append1[Index]
      RETURN 
      
      Var NewEndDate =
      CALCULATE(
      	MAX( Append1[End Date]),
      	FILTER(
      		ALL( Append1),
      		CurrentIndex =  Append1[Index] +1
      	)
      )
      RETURN 
      SWITCH( TRUE(),
      Append1[Type] = "Actual",Append1[Start Date],NewEndDate)
      
      

      Basically says if the  Type is "Actual" give me the original start date, if it says anythign else besides "Actual" give me the previous row's ( which is why we needed the index column) end date as the new starting date.  The original end date can stay
   3. :

[LivioLanzo]

Hi @Anonymous

could you show me what the end result should be in your case?

[Anonymous]

Sure, Not a problem. 

I threw together the table below that shows how the info would look when combined. If you can see, for Product X it was on Line 2 today but should have been on line 4. I want to switch back to the forecast version going forward as shown. 

I didn't think about it but I would likely label the source as shown in the table below. 

Hope this helps. 

Name	Start Date	End Date	ID	Production Line	Source
Product X	10/1/2018	10/7/2018	11	Line 1	Actual
Product X	10/7/2018	10/17/2018	11	Line 2	Actual
Product X	10/17/2018	10/19/2018	11	Line 4	Actual
Product X	10/19/2018	11/20/2018	11	Line 2	Actual
Product X	10/20/2018	12/29/2018	11	Line 4	Forecast
Amy	10/6/2018	10/16/2018	22	Line 3	Actual
Amy	10/16/2018	11/20/2018	22	Line 4	Actual
Amy	11/20/2018	12/31/2018	22	Line 4	Forecast