how to build this model data

[TPC]

Hi,

I have an inventory report that is extracted on a monthly basis. 

So the column headers remain the same each month except the data. 

There is no date reference available in the files.

My goal is to store the monthly inventory files in a folder (get data from folder containing the files with monthly add on, so the folder will contain Jan 2023, Feb 2023, Mar 2023, etc inventory files, and they are combined into one spreadsheet to track inventory evolution.

Any advice on how the data model can be built? To note that date reference is not available in the data. 

[HughLa]

Hi @TPC 

Have a look at this question: https://community.powerbi.com/t5/Desktop/adding-the-file-name-to-a-column-in-power-query/td-p/212533...

Hope it helps you.

Hugh

[TPC]

hi @HughLa ,

Thanks alot! And that is the solution I needed. However, I have one further question. After the date column is extracted, it is not in date format. When I left click on the top left hand of the "date column" that has been created to extract the date from the file name, it gives an error. Could you show me the way to solve this please?

[HughLa · Accepted solution]

Hi @TPC 

Glad to hear that it is what you need.

What you can do is create a new column from example and extract the date from the file name. 

In the screenshot below, I generated the Date column form the Name column. 

Hope that you come right with this.

Hugh