Substitute a value under conditions

[mechanix85]

Hello. I have table1 and table2 (reference book). To table 1 it is necessary to put a column " value" from Table 2, if Т1date is in an interval between dates in Т2 and numbers correspond

T1

Spoiler

date number
01.01.17 1,00
02.01.17 1,00
03.01.17 2,00
04.01.17
05.01.17
06.01.17 2,00
07.01.17 2,00
08.01.17 2,00
09.01.17 2,00
10.01.17 2,00
11.01.17 2,00
12.01.17 2,00
13.01.17 2,00
14.01.17 3,00
15.01.17 3,00
16.01.17 3,00
17.01.17 3,00
18.01.17
19.01.17
20.01.17
21.01.17
22.01.17
23.01.17
24.01.17
25.01.17

 T2

Spoiler

from to number value
02.01.17 03.01.17 1,00 50,00
08.01.17 19.01.17 2,00 75,00
08.01.17 19.01.17 3,00 150,00

 Result

 Sorry for my english, I write through translate.google.com

[vanessafvg]

@mechanix85 struggling a little bit to understand what you are saying

might help if you show what your expected results are

[mechanix85]

if the column "date" from T1 is in the interval of the dates T2 (from to) and the column "number" from T1 = column "number" from T2, then it will get the value from the column "value" T2

in excel formula

=SUMPRODUCT((table2[from]<=table1[@date])*(table2[to]>=table1[@date])*(table2[number]=table1[@number])*table2[value])

[vanessafvg]

@mechanix85 now i get it 🙂

what about a calculated column

test =
CALCULATE (
    VALUES ( number ),
    FILTER ( T2, T1[date] >= T2[from] && T1[date] <= T2[to] )
)

[mechanix85]

Thanks, but I know how to get the result using DAX, I would like to do this with the help of a power query

[vanessafvg]

@mechanix85 ah i see, ok i thought you said you wanted to improve performance, maybe someone like @MarcelBeug can help.

[MarcelBeug · Accepted solution]

My suggestion would be to expand Table2 so you will have a value for each date and you can just merge both tables directly on keys "date" and "number".

This can be done quite easily by importing the table with ALL number type fields (also the date fields!!), add a custom column with lists of values that represent all dates from "from" to "to" (both inclusive), expand this list column, adjust the data type to date and select/reorder only the required columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"from", type number}, {"to", type number}, {"number", type number}, {"value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "date", each {[from]..[to]}),
    #"Expanded date" = Table.ExpandListColumn(#"Added Custom", "date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded date",{{"date", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"date", "number", "value"})
in
    #"Removed Other Columns"

[mechanix85]

@MarcelBeug, Thanks, I wrote above the code above. We shall stop on this variant 

let
    source = Excel.CurrentWorkbook(){[Name="table2"]}[Content],
    #"ch type" = Table.TransformColumnTypes(source,{{"from", type date}, {"to", type date}, {"number", type number}, {"value", type number}}),
    #"dates" = Table.AddColumn(#"ch type", "date", each List.Dates([from],Number.From([to])-Number.From([from])+1,#duration(1,0,0,0))),
    #"dates Expand" = Table.ExpandListColumn(#"dates", "date"),
    #"ch type2" = Table.TransformColumnTypes(#"dates Expand",{{"date", type date}})
in
    #"ch type2"

[mechanix85]

As an option, I made a column of dates from Table 2 using List.
And then you can merge two tables

But I think there should be a more refined move, because My table of 1600 lines turned out already at 200,000

let
    source = Excel.CurrentWorkbook(){[Name="table2"]}[Content],
    #"ch type" = Table.TransformColumnTypes(source,{{"from", type date}, {"to", type date}, {"number", type number}, {"value", type number}}),
    #"dates" = Table.AddColumn(#"ch type", "date", each List.Dates([from],Number.From([to])-Number.From([from])+1,#duration(1,0,0,0))),
    #"dates Expand" = Table.ExpandListColumn(#"dates", "date"),
    #"ch type2" = Table.TransformColumnTypes(#"dates Expand",{{"date", type date}})
in
    #"ch type2"

[mechanix85]

I created (in the first in my life) function for table2

/*query name=
fn_table2*/

(date as date, cod as number) =>
let
    source = Excel.CurrentWorkbook(){[Name="table2"]}[Content],
    #"Changed type" = Table.TransformColumnTypes(source,{{"from", type date}, {"to", type date}, {"number", type number}, {"value", type number}}),
    #"Strings with applied filter" = Table.SelectRows(#"Changed type", each [from] <= date and [to] >= date and [number]=cod)
in
    #"Strings with applied filter"

And then called it to request a table1

/*query name=
table1*/

let
    source = Excel.CurrentWorkbook(){[Name="table1"]}[Content],
    #"Changed type" = Table.TransformColumnTypes(source,{{"date", type date}, {"number", type number}}),
    #"fn" = Table.AddColumn(#"Changed type", "Custom", each try fn_table2([date], [number]) otherwise null),
    #"AddCustom" = Table.ExpandTableColumn(#"fn", "Custom", {"value"}, {"Custom.value"})
in
    #"AddCustom"

This all works, however, when I started the query on my database (table1 = 100000 rows, table2 = 1600), the query works very long.

Are there options to speed up the work?