Power BI - Store each latest date (refresh history) to a table OR other hack in power bi desktop

Question

I am about to lose my head to solve the following issue. It would be easy for members who usually use Power Query, but not for me, as i have done a little work with it (I have always used DAX).

I'm facing an issue related to data where i only have the first two columns ["Requirements", "Values"] of the following dataset as an example, and my target is to add a refresh date or some other hack that whenever my data got a refresh, a date is added here which could connect to Calendar table. Later, i will create a line chart that shows the percentage of "Requirements" linked to the "Values". Each time the report gets updated, the line chart shows a percentage trend with a new refresh date added to the table.

And this is the end goal I want to achieve.

Can you please guide me if there is any hack/way around to achieve it in Power BI?

Answer 1

Assuming strictly one extract is performed on every calendar day from StartDate (which would be a Power Query parameter of type date containing the adequate date), you could create a column "Refresh Date" like this (and then type it in an additional step):

= Table.FromColumns(Table.ToColumns(PreviousStep) & {List.Combine(List.Transform({0..Table.RowCount(PreviousStep)/2 - 1}, each {Date.AddDays(StartDate, _), Date.AddDays(StartDate, _)}))}, Table.ColumnNames(PreviousStep) & {"Refresh Date"})

If it is only extracted on some kind of working days, then you should adjust this column (probably as a simple day counter) and correlate it to some working days counter column in your calendar.

Here are a few explanations (for one extraction per calendar day with rows ordered chronologically):

• Generate a list with all extraction days (from 0 to Nb of days - 1) with "{0..Table.RowCount(PreviousStep)/2 - 1}".
  
• Then Duplicate each nb in this list as you have two lines for the same day.
  
  • Actually "List.Transform(...)" generates a list of lists {{0, 0}, {1, 1},...} and then add these numbers to StartDate (typ. to 2024-03-01).
    
  • In the end, we get {{2024-03-01, 2024-03-01}, {2024-03-02, 2024-03-02},...}.
    
• List.Combine gives one single list thanks to List.Combine: {2024-03-01, 2024-03-01, 2024-03-02, 2024-03-02,...}.
  
• Then we can add a list to the table as a new column through Table.FromColumns but this works only if we first convert the table as a list through Table.ToColumns.