Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
naoyixue1
Post Patron
Post Patron

Load multiple excels but results is not correct

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)

 

naoyixue1_0-1694919888931.png

naoyixue1_1-1694919997617.png

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. 

 

naoyixue1_2-1694920112347.png

 

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_4-1694920236356.png

 

 

7 REPLIES 7
naoyixue1
Post Patron
Post Patron

@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 ) 

 

naoyixue1_0-1694964927921.png

naoyixue1_1-1694965152213.png

 

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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 

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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. 

 

naoyixue1_0-1695529520927.png

 

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.