Forum Discussion

peterpan's avatar
peterpan
Helper 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 duration as mentioned in Table A. The rates we are fetching should correspond to its applicability duration as specified in Table A. For eg, in Sr. No. 1 in Table B, the rate would be 500 and in 2., it would be 700.

 

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

4 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    peterpan 

    let
    tblB = your_table_B,
    tblA = Table.AddColumn(your_table_A, "Date", each [Start Date]),
    combine = tblB & tblA,
    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
    Helper 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
      Super 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
        Helper 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!