Load multiple excels but results is not correct

[naoyixue1]

Hey there,

I tried to load the last 30 days production sheet into BI. But the results seems to be incorrect. We pulled those sheets from planning system and name it with that date.  (see below example). Each file only has the YTG info on a weekly bucket. For example, we will only see the production info strating from 9/15/23, if we open the file generated on 9/15)

But when I loaded into BI, I saw the weekly info starting from the oldest report.  ( Since I have two files in the test folder one is 07302023 and 09152023), But after I loaded the data into BI, I saw i had the info in the report of 09152023, which is incorrect. 

Do you know why? That's the steps I loaded those files.  I clicked to combine the file. I'm thinking that step is wrong. Your thoughts?  Thanks!

[naoyixue1]

@Greg_Deckler Understood. But since the file generated in the week of Sep.15th, I'm not supposed to see any YTD data. But unfortunately I saw those YTD data in the Sep.15th file in BI, after I combine all files together. In other words, the combine function doesn't work as expected. (See below example, I saw the data before the week of 9/15 in BI after combining two files. But this file in excel only should have the data starting from the week of 9/10/23 ) 

[Greg_Deckler]

@naoyixue1 OK, but if the other file had the YTD information in it, you would see it. If you only want to process the last file in a folder then you would need to insert some filtering during the file ingest. So basically flag the latest file date and then filter out any others. 

Perhaps you are getting confused by the "First file" statement. That simply means that it is using the first file as the sample file when performing transformations. But, then those transformations get applied to all files and then they are combined into a single table.

It seems like the combine files is working as expected as, again, when you combine files you are going to combine all rows from all files. So, somewhere in those files you have the YTD information. I can't imagine Power Query is just inventing rows of data out of nowhere.

[naoyixue1]

@Greg_Deckler Got it. That makes sense. But now I want to create the reports in power bi to compare different planning sheet generated within last 30 days. That's why I originally wanted to load last 30 days file and combine them into one big report, so I can compare the value based on when that report is generated. But if that way doesn't work, is there any work around I can do in BI to do such comparison? Thanks 

[Greg_Deckler]

@naoyixue1 Sure, the specifics are going to depend on your particular data model but here is a video that shows all kinds of different date intelligence calculations (see below). The basic pattern is something like the following:

Total Last 30 Days Measure = 
  VAR __MaxDate = MAX('Table'[Date])
  VAR __MinDate = __MaxDate - 30
  VAR __Table = FILTER( ALL( 'Table' ), [Date] >= __MinDate && [Date] <= __MaxDate )
  VAR __Result = SUMX( __Table, [Value] )
RETURN
  __Result

[naoyixue1]

@Greg_Deckler Thanks, but i don't think that can solve my porblem.  We set query to daily extract the data from planning syste.  Each vesion just has the Day to go information. I want to load the last 30 days individual files into BI, so I can make the comparsion between two different snapshot of time when we extract the info from planning system. That's why I originally applied the combine file Unfortunately it always pulls the oldest file Day to Go info.....

To me, that measure doesn't support the comparsion purpose for any two different snapshot of the file. But thanks for your heklp. 

[Greg_Deckler]

@naoyixue1 Well, if it were me, I would load the files but keep the "Date modified" column as part of the table that you end up with. That way, you could compare any day to any other day or compare the latest day to the 30 days ago. That would be my approach if you don't have a date in your Excel files.

[Greg_Deckler]

@naoyixue1 If you combine files, you will end up with a single table with all of the rows from all of the files in a folder. Is that not what you want?