Date query in Powerquery

[ErsinALACA]

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

TableA;

TableB (Wanted)

Thanks for answers.

 

[sandy666]

or maybe this : Power Query Approximate Match

 

[ErsinALACA]

Dear Sandy666;

First of all, thank you for your answer. Your answer in Post # 7 is for FULL MATCH. I can do this with the query join method. I want to do; Let's continue on your example ... Let the date in TableB be 15.03.2019. Since the date range of 15.03.2019 is in the range of 14.03.2019 and 18.03.2019, let's return 5.2 and 6.2 again. The value is returned as null because it is FULL MATCH. I hope I was able to explain more precisely, "Google Translate" could explain ) Thanks again for your interest and help.

 

[ErsinALACA]

Dear Sandy666;

I will work on the link you posted in Post # 11. It looks like the solution I want. I will share it here if I can. Thanks.

 

[ErsinALACA]

I'm at work right now, I took a look at the example in the link. I will apply the sample to my question at a convenient time. I was really surprised that the "Fill Down" method would be the solution for this question.

Thank you very much again; Dear sandy666

 

[sandy666]

You are welcome
Rica ederim
Thanks for the feedback
Geri dönüşünüz için teşekkür ederiz

 

[ErsinALACA]

Thank you very much for your kind understanding and translation. ?

 

[sandy666]

?

 

[ErsinALACA]

Dear sandy666;

I was inspired by the link you provided. I created the following codes. Maybe a shorter method is of course available. I really want to solve this problem besides filtering and sorting. But this is my knowledge in the power question. This problem; I'd better bring the first record of PartA as the first record of PartB. But I also know that this kind of power query works very slowly. If we do the same with the "ARRAY" method in the macro, the max. While it can be done with 3-5 seconds, it can be 3-5 minutes depending on the data size. Anyway, my modest and unpretentious codes ;

// TableA
let
    Source = Excel.CurrentWorkbook(){[Name="TableA"]}[Content]
in
    Source
    
// TableB
let
    Source = Excel.CurrentWorkbook(){[Name="TableB"]}[Content]
in
    Source

// Sorgu1
let
    Kaynak = Table.NestedJoin(TableA,{"Date"}, TableB, {"Date"}, "TableB", JoinKind.FullOuter),
    #"Değiştirilen Tür1" = Table.TransformColumnTypes(Kaynak,{{"Date", type date}, {"Price", type number}}),
    #"Genişletilen TableB" = Table.ExpandTableColumn(#"Değiştirilen Tür1", "TableB", {"Date"}, {"TableB.Date"}),
    #"Değiştirilen Tür" = Table.TransformColumnTypes(#"Genişletilen TableB",{{"TableB.Date", type date}}),
    #"Sıralanan Satırlar" = Table.Sort(#"Değiştirilen Tür",{{"Date", Order.Descending}}),
    #"Aşağı Dolduruldu" = Table.FillUp(#"Sıralanan Satırlar",{"TableB.Date"}),
    #"Özel Eklendi" = Table.AddColumn(#"Aşağı Dolduruldu", "Wanted :))", each if [Date] <> null and [TableB.Date]>=[Date] then [Price] else 0, type number),
    #"Filtrelenen Satırlar" = Table.SelectRows(#"Özel Eklendi", each ([#"Wanted :))"] <> 0)),
    #"Kaldırılan Yinelenenler" = Table.Distinct(#"Filtrelenen Satırlar", {"Part"}),
    #"Kaldırılan Sütunlar" = Table.RemoveColumns(#"Kaldırılan Yinelenenler",{"Date", "Price"}),
    #"Sıralanan Satırlar1" = Table.Sort(#"Kaldırılan Sütunlar",{{"Part", Order.Ascending}})
in
    #"Sıralanan Satırlar1"

 

[sandy666]

I also know that this kind of power query works very slowly. If we do the same with the "ARRAY" method in the macro, the max. While it can be done with 3-5 seconds, it can be 3-5 minutes depending on the data size.

Your choice ?
but what will you do if your company prohibits using vba?
IMO, Power Query is much more flexible
but this is my private opinion only

my ranking is:

1. Power Query
2. Pivot Table
3. Worksheet function
4. -
5. Excel features
6. Power Pivot
7. -
8. -
9. Power BI
10. -
11. -
12. -
13. -
14. vba

??

 

[ErsinALACA]

Dear sandy666;

"The Power Query is much more flexible." This is our common view, "PowerQuery" this is what attracts. However, it is an undeniable fact that "PowerQuery" needs further development. For example, case sensitivity of functions. When you write a function as I want; Calculating data in a table of 10-15 rows takes 3 minutes without exaggeration, it is really slow. I will send another example to support this thesis in the next post ...

The sample is stuck on the computer at the workplace