Forum Discussion
Solved
Fetching Data based on Start Date & End Date
I have two tables- Table A: Table B: I want to bring rates for companies as mentioned in Table A in Rates column of Table B. There could be multiple rates for a varied durat...
peterpan null is reserved for Table.FillDown. So I used "none". You are free to replace it with anything else in the code but null. Or simply replace "none" with null after all transformations.
let tblB = your_table_B, tblA = Table.AddColumn(your_table_A, "Date", each [Start Date]), diff = Table.RemoveMatchingRows(tblB, Table.ToRecords(tblA), "Company"), missing = Table.FromRows( Table.ToList(diff, (x) => x & {"none"}), {"Company", "Start Date", "Rates"} ), combine = tblB & tblA & missing, sorted = Table.Sort(combine,{{"Company", Order.Ascending}, {"Date", Order.Ascending}, {"Rates", Order.Descending}}), f_down = Table.FillDown(sorted, {"Rates"}), filter = Table.SelectRows(f_down, each ([Start Date] = null)), z = Table.SelectColumns(filter, Table.ColumnNames(tblB) & {"Rates"}) in z
peterpan null is reserved for Table.FillDown. So I used "none". You are free to replace it with anything else in the code but null. Or simply replace "none" with null after all transformations.
let
tblB = your_table_B,
tblA = Table.AddColumn(your_table_A, "Date", each [Start Date]),
diff = Table.RemoveMatchingRows(tblB, Table.ToRecords(tblA), "Company"),
missing = Table.FromRows(
Table.ToList(diff, (x) => x & {"none"}),
{"Company", "Start Date", "Rates"}
),
combine = tblB & tblA & missing,
sorted = Table.Sort(combine,{{"Company", Order.Ascending}, {"Date", Order.Ascending}, {"Rates", Order.Descending}}),
f_down = Table.FillDown(sorted, {"Rates"}),
filter = Table.SelectRows(f_down, each ([Start Date] = null)),
z = Table.SelectColumns(filter, Table.ColumnNames(tblB) & {"Rates"})
in
z
I think this would work. Thanks. Just that we would have to keep unique values in Sr. No. at the end since we are combining missing & tableb together and filtering out start date wont remove extra row since there is no value in their for none values. Very helpful!