Need Help of Data Load

Power Query Data Setup
1

Here is the Sample Data table where I have to load every Monday to get the data table as below in Power BI, I copy paste in Excel every Monday the new data set and Manually enter the date of Monday(As on date column).

  1. So my Question is there a way the Monday date gets automatically populated ?
  2. Is there a version control where I don’t have to paste the latest data below the previous Data manually every Monday but still can see the data of previous Mondays(As on date)

Excel table…

ID Name Status Country Hire Date As On Date
121 Merritt Aydin Active India 31-Jul-21 02-Aug-21
122 Spear Robert Active USA 30-Jul-21 02-Aug-21
123 Willard James Active LATAM 01-Aug-21 02-Aug-21
124 Tot Maria Active India 01-Aug-21 02-Aug-21
125 Wells Paul Active USA 01-Aug-21 02-Aug-21
126 Collier Matilda Active India 01-Aug-21 02-Aug-21
127 Ramjac Wolfgang Active USA 06-Aug-21 09-Aug-21
128 Ramsy Peter Active USA 06-Aug-21 09-Aug-21
121 Merritt Aydin Active India 31-Jul-21 09-Aug-21
122 Spear Robert Active USA 30-Jul-21 09-Aug-21
123 Willard James Inactive LATAM 01-Aug-21 09-Aug-21
124 Tot Maria Active India 01-Aug-21 09-Aug-21
125 Wells Paul Active USA 01-Aug-21 09-Aug-21
126 Collier Matilda Inactive India 01-Aug-21 09-Aug-21
127 Ramjac Wolfgang Active USA 06-Aug-21 16-Aug-21
128 Ramsy Peter Inactive USA 06-Aug-21 16-Aug-21
121 Merritt Aydin Active India 31-Jul-21 16-Aug-21
122 Spear Robert Active USA 30-Jul-21 16-Aug-21
123 Willard James Inactive LATAM 01-Aug-21 16-Aug-21
124 Tot Maria Active India 01-Aug-21 16-Aug-21
125 Wells Paul Active USA 01-Aug-21 16-Aug-21
126 Collier Matilda Inactive India 01-Aug-21 16-Aug-21
129 Ranm Active LATAM 13-Aug-21 16-Aug-21
130 Hari Active USA 13-Aug-21 16-Aug-21

Solution Table

image

2

You could achieve that for example with this piece of M code.
Date.StartOfWeek( Date.From( DateTime.FixedLocalNow() ), Day.Monday )

Only problem, is that every time this code is evaluated it returns the Monday of the current week.
You can also pass a static date using the intrinsic #date function; #date(2021, 8, 30)

Guess this has already been answerd above…
However if you are using Excel, you can use these shortcuts to:

  • enter the current date Ctrl+; (semi-colon)
  • copy that value in a contignious range Ctrl+D (filll down)

or explore VBA or Office Scripts to automate that process

I hope this is helpful

1 Like
3

I have accomplished something similar in the past by pulling the data in from a folder (just add a new file each time).

You can combine the files keeping the file creation date, the file update date, or a date included in the file name.

The attached shows how I have combined the three Excel files keeping the update date in one table, and using the filename trick with the other table.

save the files to a folder in your system, and update the parameter to your proper file path to see the solution.

04-28-21 File.xlsx (17.2 KB)
05-03-21 File.xlsx (17.1 KB)
06-06-21 File.xlsx (17.0 KB)
File Date included in query.pbix (22.7 KB)

1 Like
4

Hi @Dharma, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

5

Hi @Dharma, we’ve noticed that no response has been received from you since August 30.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Hi @Dharma, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.