Forum Discussion

More_BI's avatar
More_BI
Frequent Visitor
9 years ago

Pull in only latest data from data source

Hey guys,

 

I currently scan through various folders and pull in a series of spreadsheets that are generated each month with the latest KPI data. The problem arises during the reporting period when we have incomplete data. The reports look terrible because there are a bunch of blanks for the latest reporting period. To get around this, I created several queries to transform the data to only pull in the latest available data for that KPI:

 

  1. Import data
  2. Identify latest two reporting periods (dedupe period, sort, take top two results) -> auxilary table
  3. Split out data into two tables
    1. Latest Period Records
    2. Previous Period Records
  4. Outer Join Latest Period Records on Previous Period Records 
  5. Determine if the Latest Period Record exists for each row (true/false)
  6. Create another set of columns to the right with the latest record according to this flag. 

 

This is definitely not efficient and there has to be a better way to do it. I would like to simplify the process because a new excel format is being introduced into the data collection effort which is complicating the queries. How can I simplify this process?

 

Thanks!

 

 

Need Help

3 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    You can easily select only those rows of a table who have the latest value in the [Period] like this:

     

    Table.SelectRows(Table, each [Period]=List.Max(Table[Period]))

    This assumes that the natural sort order of the values in Column "Period".

    If that's not the case, you can check if rows are returned once you select the latest period (and take those then) and if it is empty, filter the previous period like this:

     

    if Table.RowCount(Table.SelectRows(Table, each [Period]=LastPeriod)) >0 then Table.SelectRows(Table, each [Period]=LastPeriod) 
    									else Table.SelectRows(Table, each [Period]=PreviousPeriod)
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi More_BI,

     

    Is ImkeF suggestion helpful to your scenario? If so, please kindly mark the corresponding reply as an answer so that it can benefit others having similar requirements. If you still have any question, please feel free to ask.

     

    Thanks,
    Yuliana Gu

    • More_BI's avatar
      More_BI
      Frequent Visitor
      9 years ago

      Well, not entirely. It is the same way I'm doing it now. It's just many, many steps that I was hoping to reduce. Not a huge deal since it works, but I would have loved to simplify it somehow.

       

      To clarify, my step #5 is the what was described in the response above. I didn't use that exact syntax but more-or-less the same logic where I created a column to check to see if there was a value in the Latest.[Column Name] then set to TRUE. Then I created a third set of columns with the latest available value (keying off my new column to tell me which version to use). I'm not too happy with this method, but again, it works.