Date query in Powerquery

[ErsinALACA]

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

TableA;

TableB (Wanted)

Thanks for answers.

 

[sandy666]

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[sandy666]

here is

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

 

[sandy666]

extended version

Table1				Table2		Result
Date	Part	Price		Date		Date	Part	Price			out of range
02/03/2020	Part1	3		20/03/2020		25/02/2020	Part1	3			exact match
02/03/2020	Part2	5		28/03/2020		05/03/2020	Part3	7		rest	approx. match
05/03/2020	Part3	7		25/02/2020		05/03/2020	Part8	23
09/03/2020	Part4	11		11/04/2020		20/03/2020	Part4	11
25/03/2020	Part5	13		05/03/2020		20/03/2020	Part5	13
25/03/2020	Part6	17				28/03/2020	Part6	17
31/03/2020	Part7	19				28/03/2020	Part7	19
05/03/2020	Part8	23				11/04/2020	Part7	19

if date(s) from Table2 exist in Table1 then result will be this date and data - Exact Match
if date(s) from Table2 doesn't exist in Table1 then result will be first and last date and data from the range of Table1 - Approx. Match
if date(s) from Table2 doesn't exist in Table1 but exist in range of dates in Table1 then result will be the first date before/after the date from Table2 - Approx. Match

 

[ErsinALACA]

Yes, exactly as I wanted, Thank you sandy 666. I can't see the codes, where?

 

[sandy666]

I was hoping to see your code first ?

 

[ErsinALACA]

I was hoping to see your code first ?

I'm not a master, I'm very new at Powerquery. I can add new columns and do things with built-in functions, but I can't write code. I keep getting errors all the time. I am having trouble implementing the functions I display with "= # Shared". "I can't turn into a table" error every time is really annoying

 

[sandy666]

ok,

// Query1
let
    Tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Base = Table.ExpandTableColumn(Table.NestedJoin(Tbl2,{"Date"},Tbl1,{"Date"},"Tbl1",JoinKind.FullOuter), "Tbl1", {"Date", "Part", "Price"}, {"Date.1", "Part", "Price"}),
    Conditions = Table.AddColumn(Base, "Custom", each if [Date] = null then [Date.1] else [Date]),
    FillU = Table.FillUp(Table.SelectColumns(Table.Sort(Conditions,{{"Custom", Order.Ascending}}),{"Custom", "Part", "Price"}),{"Part", "Price"}),
    FillD = Table.FillDown(Table.SelectColumns(Table.Sort(Conditions,{{"Custom", Order.Ascending}}),{"Custom", "Part", "Price"}),{"Part", "Price"}),
    Mrg = Table.NestedJoin(Tbl2,{"Date"},Table.Combine({FillU, FillD}),{"Custom"},"Ap",JoinKind.LeftOuter),
    ExpandR = Table.Distinct(Table.ExpandTableColumn(Mrg, "Ap", {"Part", "Price"}, {"Part", "Price"})),
    Type = Table.TransformColumnTypes(Table.SelectRows(Table.Sort(ExpandR,{{"Date", Order.Ascending}, {"Part", Order.Ascending}, {"Price", Order.Ascending}}), each ([Price] <> null)),{{"Date", type date}})
in
    Type

but you have to figure out how it was done by yourself
note: dates and data can be in random order in both source tables

 

[sandy666]

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[ErsinALACA]

but you have to figure out how it was done by yourself

I can easily do anything I want to do in VBA. Unfortunately I can't show 10% of the same success in Powerquery. Annoying error messages, case sensitivity. What attracted me is the easy data formatting feature. It can format data in SAP systems easily and quickly. Especially in date type data. In Excel, you need to write a formula like MID etc.

Thank you for codes ?

 

[sandy666]

You are welcome
Thanks for the feedback