Date query in Powerquery

[ErsinALACA]

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

TableA;

TableB (Wanted)

Thanks for answers.

 

[sandy666]

hm, so I prefer my solution where I can enter a few dates in Table B
see post#27, small green table

 

[ErsinALACA]

If I can, I prefer it too. I'm sorry, I have no information. Let's wait, maybe another friend can suggest a solution

 

[sandy666]

not optimised to single query yet, I am too lazy ? ?

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

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

// Merge1
let
    Source = Table.NestedJoin(Table2,{"Date"},Table1,{"Date"},"Table1",JoinKind.FullOuter),
    Expand = Table.ExpandTableColumn(Source, "Table1", {"Date", "Part", "Price"}, {"Date.1", "Part", "Price"}),
    Conditions = Table.AddColumn(Expand, "Custom", each if [Date] = null then [Date.1] else [Date]),
    Sort = Table.Sort(Conditions,{{"Custom", Order.Ascending}}),
    TSC = Table.SelectColumns(Sort,{"Custom", "Part", "Price"}),
    FillD = Table.FillDown(TSC,{"Part", "Price"}),
    FillU = Table.FillUp(FillD,{"Part", "Price"})
in
    FillU

// Merge2
let
    Source = Table.NestedJoin(Table2,{"Date"},Merge1,{"Custom"},"Merge1",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Merge1", {"Part", "Price"}, {"Part", "Price"}),
    Distinct = Table.Distinct(Expand),
    Type = Table.TransformColumnTypes(Distinct,{{"Date", type date}})
in
    Type

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

 

[ErsinALACA]

Sevgili sandy666; tebrikler.

Part1, Part2, Part3 ... When partn parts are sold at Table1 on the same date, can we see all parts prices as unique value?

 

[sandy666]

you didn't say that in the "first" post where should be detailed description
so just test it

I did approximate match only

 

[ErsinALACA]

Understood. In the table I sent in # 26, there are two different parts prices on each date. I wanted to see them if there were two or more. No need if it's hard. I wanted to take examples for more difficult queries.

 

[sandy666]

combine the right pieces of code and try to do it yourself. I can't do everything as ready-made-solution.

maybe tomorrow or later I'll think about it

 

[ErsinALACA]

You are right. Thank you very much for your time.

 

[sandy666]

you are most welcome
have a nice day ?

 

[ErsinALACA]

Same to you. I hope one day he learns PowerQuery as much as you do and I can help other friends too here.