Forum Discussion
Substitute a value under conditions
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
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
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
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"
9 Replies
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?
mechanix85 struggling a little bit to understand what you are saying
might help if you show what your expected results are
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])
mechanix85 now i get it :)
what about a calculated column
test =
CALCULATE (
VALUES ( number ),
FILTER ( T2, T1[date] >= T2[from] && T1[date] <= T2[to] )
)
