Date query in Powerquery

[ErsinALACA]

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

TableA;

TableB (Wanted)

Thanks for answers.

 

[sandy666]

btw. did you try with single date in TableB ?

I will send another example to support this thesis

this is not necessary, you won't convince me, neither will I convince you

 

[ErsinALACA]

btw. did you try with single date in TableB ?

In the codes I sent; When I wrote a single date in TableB, the codes worked. Have you seen a problem?

 

[sandy666]

TableB = Table2
TableA = Table1

 

[sandy666]

I think I found it
you've FillUp
but you don't have FillDown

it is hard to understand the language mix

    #"Aşağı Dolduruldu" = Table.FillUp(#"Sıralanan Satırlar",{"Table2.Date"}),
    #"Filled Down" = Table.FillDown(#"Aşağı Dolduruldu",{"Table2.Date"}),

 

[ErsinALACA]

Yeah, I used "Fillup" ?

it is hard to understand the language mix

 

[ErsinALACA]

Maybe it can be useful to friends. Table layout and codes exactly as follows;

// 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]

one more thing...
where is 20/03/2020 in your table (the bigger green) ?

Date	Part	Price		Date		Date	Part	Price		Part	Table2.Date	Wanted :))
02/03/2020	Part1	3		20/03/2020		20/03/2020	Part4	9		Part1	28/03/2020	3
02/03/2020	Part2	5		28/03/2020		28/03/2020	Part5	11		Part2	28/03/2020	5
05/03/2020	Part3	7								Part3	28/03/2020	7
09/03/2020	Part4	9								Part4	28/03/2020	9
25/03/2020	Part5	11								Part5	28/03/2020	11
25/03/2020	Part6	13								Part6	28/03/2020	13
31/03/2020	Part7	19

 

[ErsinALACA]

My query works with a single date. You are right. I've realized now. Please; try this sample #26.

 

[sandy666]

but still don't see date: 20/03/2020

TableB
Date		Part	Table2.Date	Wanted :))
20/03/2020		Part1	28/03/2020	3
28/03/2020		Part2	28/03/2020	5
		Part3	28/03/2020	7
		Part4	28/03/2020	9
		Part5	28/03/2020	11
		Part6	28/03/2020	13

 

[ErsinALACA]

You wrote two dates in TableB. It must be single date.

İndir Sahin

Dosyayı indir Sahin

dosya.co