Date query in Powerquery

[ErsinALACA]

Sorry my english is not enough. I will explain it simply;

TableA;

TableB (Wanted)

Thanks for answers.

 

[ErsinALACA]

Dear Sandy 666;

Thank you for your kindly understanding.

I think we have signed the biggest topic in the forum's history, but as you can see in the file I linked below, the data always brings partA and values wrong. I'm not sure I did it wrong. I would be glad if you download the file on the link.

Sahin.xlsx - 201 KB

 

[sandy666]

I know much bigger threads

ad rem
13/04/2019 taking two dates: one before and one after this date =>

Date	Part	Price
13/03/2019	Part A	5
13/03/2019	Part B	6
14/06/2019	Part A	5.2
14/06/2019	Part B	6.2
18/09/2019	Part A	5.3
18/09/2019	Part B	6.3

because of dates and part where A is before B and B is after A

for 15/06/2019 is the same as above

Date	Part	Price
13/03/2019	Part A	5
13/03/2019	Part B	6
14/06/2019	Part A	5.2
14/06/2019	Part B	6.2
18/09/2019	Part A	5.3
18/09/2019	Part B	6.3

the same for 17/07/2019

replace last line in the code with this line:
Type = Table.TransformColumnTypes(Table.SelectRows(Table.Sort(ExpandR,{{"Date", Order.Ascending}, {"Part", Order.Ascending}, {"Price", Order.Ascending}}), each ([Price] <> null)),{{"Date", type date},{"Price", type number}})

 

[sandy666]

maybe simpler will be

Date	Part	Price					Date
13/03/2019	Part A	5					15/06/2019
13/03/2019	Part B	6					13/04/2019
				13/04/2019			17/07/2019
14/06/2019	Part A	5.2
14/06/2019	Part B	6.2
				15/06/2019
				17/07/2019
18/09/2019	Part A	5.3
18/09/2019	Part B	6.3

 

[sandy666]

my mistake , previous post should be like this

Date	Part	Price			Date
13/03/2019	Part A	5			15/06/2019
13/03/2019	Part B	6			13/04/2019
			13/04/2019		17/07/2019
14/06/2019	Part A	5.2
14/06/2019	Part B	6.2
			15/06/2019
			17/07/2019
18/09/2019	Part A	5.3
18/09/2019	Part B	6.3

 

[ErsinALACA]

Thank you for your effort Dear sandy666 ?. No matter how much i thank you, it will be small for your effort.

Errors encountered after revisions to your code;

Date	Part	Price		Date		Date	Part	Price	Descriptions
13.03.2019	Part A	5		13.04.2019		13.04.2019	Part A	5	OK, (Because date; >=13.03.2019 — <14.06.2019)
13.03.2019	Part B	6		15.06.2019		13.04.2019	Part B	6,2	Must be PartB and 6
14.06.2019	Part A	5,2		17.07.2019		15.06.2019	Part A	5,2	Must be 5,2 (Because date; >=14.06.2019 — <18.09.2019)
14.06.2019	Part B	6,2		20.12.2019		15.06.2019	Part A	5,3	OK
18.09.2019	Part A	5,3				17.07.2019	Part A	5,2	Value should be 5,2 (Because date; >=14.06.2019 — <18.09.2019)
18.09.2019	Part B	6,3				17.07.2019	Part A	5,3	Value should be 6,2 (Because date; >=14.06.2019 — <18.09.2019)
						20.12.2019	Part B	6,3	OK
									20.12.2019 PartA missing

 

[ErsinALACA]

The table on the previous post is incorrect...

Date	Part	Price		Date		Date	Part	Price	Descriptions
13.03.2019	Part A	5		13.04.2019		13.04.2019	Part A	5	OK, (Because date; >=13.03.2019 — <14.06.2019)
13.03.2019	Part B	6		15.06.2019		13.04.2019	Part B	6,2	Value should be 6
14.06.2019	Part A	5,2		17.07.2019		15.06.2019	Part A	5,2	OK, (Because date; >=14.06.2019 — <18.09.2019)
14.06.2019	Part B	6,2		20.12.2019		15.06.2019	Part A	5,3	Must be PartB and Value should be 6,2
18.09.2019	Part A	5,3				17.07.2019	Part A	5,2	OK, (Because date; >=14.06.2019 — <18.09.2019)
18.09.2019	Part B	6,3				17.07.2019	Part A	5,3	Must be PartB and Value should be 6,2
						20.12.2019	Part B	6,3	OK but 20.12.2019 PartA missing

 

[sandy666]

Index	Date	Part	Price
3	13/04/2019	Part A	5		first value	13/04/2019 > 13/03/2019 AND 13/04/2019 < 14/06/2019 A is closer than B
3	13/04/2019	Part B	6.2		second value	13/04/2019 > 13/03/2019 AND 13/04/2019 < 14/06/2019 B is closer than A
6	15/06/2019	Part A	5.2		first value	15/06/2019 > 14/06/2019 AND 15/062019 < 18/09/2019 A is closer than B
6	15/06/2019	Part A	5.3		second value	15/06/2019 > 14/06/2019 AND 15/062019 < 18/09/2019 A is closer than B
7	17/07/2019	Part A	5.2		and so on…
7	17/07/2019	Part A	5.3
10	20/12/2019	Part B	6.3

it depends on sorting numbers and letters

but if you have a different proposal, I will gladly see it

 

[sandy666]

too late
the values are taken from the previous date and from the next date closer to lookup date and depend on sort of letters, ascending or descending

you can try this scenario where you define parts

Date	Part	Price		Date	Part
13/03/2019	Part A	5		13/04/2019	Part ?
13/03/2019	Part B	6		15/06/2019	Part ?
14/06/2019	Part A	5.2		17/07/2019	Part ?
14/06/2019	Part B	6.2		20/12/2019	Part ?
18/09/2019	Part A	5.3
18/09/2019	Part B	6.3

but you need to write your own M-code

 

[ErsinALACA]

I think I couldn't fully explain the incident due to language mismatch. Status; It is not a distance or proximity event. Consider the dates in Table 1 as follows:
date for product hikes. Even if the date written in Table2 is 1 day before the last day of the interval in Table1, the previous date (past date) will prevail.

You are tired because of me. Let's close the subject if you want. Thank you very much for your efforts and patience.

 

[sandy666]

If I think of something, I will post it