Date stamp weekly excel import

Data Setup
#1

Part of my weekly process involves importing Excel tables into a consolidated PBI table.

The source data does not - of course - include a date stamp.

I’d like to create a process that a) searches the existing PBI consolidated table for the max date; b) adds 7 days to that; c) imports the latest weekly data; d) creates a new column in the new data; e) populates the new column with the +7 date; and e) appends this new data into the consolidated table.

In Excel I’d do most of this with a VBA macro, but I’m not sure what the correct approach in PBI would be.

Thanks in advance for your assistance!

#2

Can you put two small samples together and a mock up of the desired outcome?

Thanks!

#3

Thanks for posting your question @brixmitch. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

#4

PBIX file “Staffing Demo” contains records for 3 staff members for two weeks - 2020-01-06 and 2020-01-13. Columns are “Date”, “Userid”, “Type”, “Organization”.

Excel file “Staffing - 2020-01-21.xlsx” contains records for 4 staff members - with all the same columns as the PBIX file except “Date”.

Goal is to determine the max date from the PBIX file (2020-01-13), add 7 days to it (2020-01-21), import the Excel data, add a column “Date”, populate “Date” with “2020-01-21”, and lastly to append the new Excel records with the date value into the main PBIX file.

Files attached, and thanksStaffing Demo.pbix|attachment (42.8 KB) Staffing - 2020-01-21.xlsx (9.3 KB)

#5

Staffing Demo.pbix (42.8 KB)

Not sure if the PBIX file uploaded

#6

And, of course, I meant “2020-01-20” instead of “2020-01-21”. apparently too early in the day for me to add “7” correctly

#7

This looks like you want to do some data manipulation on Power BI and automate that?

I usually do it with Power Query Editor. This course you can get the overview.

I recommend creating a separate date table in Power BI to easily filter your data with dates.

#8

Got a few questions for you.

  1. The weekly updates in excel, will you be adding new data to the existing worksheet and workbook?
  2. Do you intend to publish your report to the PBI Service and enable Scheduled refresh?

Thanks!

#9

Every week there’s a new Excel spreadsheet provided. I want to add the new data - plus a new date stamp - into my existing PBI file.

Current plan is desktop only.

I’m date-stamping the input so we can identify changes from week to week. Person A went from being a contractor to being an employee. Person X is a new contractor, etc.

#11

It looks like the solution is to add a column to the input sheet -

"Table.AddColumn(#"Changed Type1", "Date", each if Staffing[Date] = null then 0 else Date.AddDays(List.Max(Staffing[Date]), 7))"

and then do an append query to join the input sheet to the main sheet.

#12

There are two different ways I accomplish something like this
Version 1 - keep the file creation date with your query:

NOTE: I assume you are combining the files from a folder (that’s the easiest way to do this)
Step 1 - when you have browsed to the folder you are using, click the Transform button (not Combine and Transform)
image

Step 2 - you will now have a list of the files in the Query Editor, and you can remove all columns except for “Date Created” and “Content” (you also have options for Date modified or Date accessed if you prefer those)

image
image1036×139 11.4 KB

Step 3 - once you have the columns you don’t want to keep click the Combine Files button over content
image

This will create a ‘helper function’ to combine your files.
it will also add several steps to your current function - and give you a result that combines your data:

image
image739×323 21.7 KB

at this stage - your file does not have the “Date created” column included (the file date)
all you have to do is remove the newly added “Removed Other Columns1” and your file creation date will be added back into the query (the Content column will also be added back - but you can now safely delete that)
image

1 Like
#13

The second version is similar, but it involves including the date in the report name (as I have done with the file above), then isolating that date in a new column, and converting it into an actual date field.

then when you perform the combination, keep your newly created date field instead of the Date created field.

#14

Hi @brixmitch, we’ve noticed that no response has been received from you since the 2nd of April. 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. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!