Forum Discussion

peterpan's avatar
peterpan
Icon for Helper I rankHelper I
2 years ago
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...
Need Help
Tips and Tricks
Tutorial Requests
  • AlienSx's avatar
    AlienSx
    2 years ago

    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's avatar
peterpan
Icon for Helper I rankHelper I
2 years ago

AlienSx 

Thank you so much! It worked. However, if there's any other company name which is not there in TableA, it would fetch incorrect rate. Is there a way to bring null in such cases so that accuracy can be maintained?

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      peterpan
      Icon for Helper I rankHelper I
      2 years ago

      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!