Caclulation of End Data in desired format

[L70F]

Hi, I  work with Power Query and need help to get the "End date" calculated as the "Start date"-1. 

I need to get it in format YYYYMMDD. 

The start date will be different every time I will run this PQ since the source of the table will be updated with new data. 

Start date	End date
20200107	20200106
20200301	20200229

Thank you for your help

BR//L70F

[Jimmy801 · Accepted solution]

Hello @L70F 

i don't see any big issue to this. First the Start Date has to be transformed to a real date, then subratacted by 1 day and afterwards again transformed to your desired output format. Here the complete solution

let
	Source = #table
	(
		{"Start date"},
		{
			{"20200107"},	{"20200301"}
		}
	),
	AddColumn = Table.AddColumn
	(
		Source,
		"End date",
		(dateint)=> let 
			ToDate =  Date.From(dateint[Start date], "de-DE"),
			Subtract1Day = Date.AddDays(ToDate, -1),
			ToText = Date.ToText(Subtract1Day,"YYYYMMDD")
		in 
			ToText,
		type number

	)
	
in
	AddColumn

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[edhans]

I feel your pain. My ERP system uses the same pseudodate format. 😐 The easiest way to do this is with multiple columns, but here is one big formula that will do it. 

Date.Year(Date.AddDays(#date(Number.IntegerDivide([Start date],10000),Number.IntegerDivide([Start date],100)-Number.IntegerDivide([Start date],10000) * 100,[Start date] - Number.IntegerDivide([Start date],100)*100),-1)) * 10000 +
Date.Month(Date.AddDays(#date(Number.IntegerDivide([Start date],10000),Number.IntegerDivide([Start date],100)-Number.IntegerDivide([Start date],10000) * 100,[Start date] - Number.IntegerDivide([Start date],100)*100),-1))*100 +
Date.Day(Date.AddDays(#date(Number.IntegerDivide([Start date],10000),Number.IntegerDivide([Start date],100)-Number.IntegerDivide([Start date],10000) * 100,[Start date] - Number.IntegerDivide([Start date],100)*100),-1))

 Basically, you have to deconstruct the first date, so the Number.IntegerDivide is going through and getting the year, then month, then you have to subtract those from the original values to work your way into three separate segments. You essentially end up with a #date(2020,1,7) function for the current date. Then you just do Date.AddDays(date,-1) to move it back. Then you have to put it back into the integer format. So the Date.Year is taking the year * 10,000, then adding to the Date.Month() value * 100, + Date.Day() value.

You can see why multiple columns would make this easier as you have much less repetition. You could also do this by converting the integer to text, and using Text.Start/Text.Middle/Text.End to extract the portions of the date, but then you have to convert back to numbers, still use the Date.Year(#date(blah,blah,blah)) values. 

You know, thinking through it, converting the whole thing to text then date might be easier. Sorry, thinking as I type. 

=Date.Year(Date.AddDays(Date.FromText(Text.From([Start date])),-1)) * 10000 +
Date.Month(Date.AddDays(Date.FromText(Text.From([Start date])),-1)) * 100 +
Date.Day(Date.AddDays(Date.FromText(Text.From([Start date])),-1))

The Date.FromText(Text.From([Start date])) function will convert a YYYYMMDD integer to a date. I avoid this because this won't fold for SQL Server queries, but it is much faster and if folding isn't relevent, this is also easier to read. 😊

Here is my PBIX file with both methods in use.