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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Summing up files extracted from SharePoint folder

I have imported the contents of a SharePoint folder that contains several Excel files.  After import, the contents are extracted and each of these files (and their respective rows of data) show up under the column Source.Name.    This column acts as an identifier column so you can identify each data set.   So in the Query Editor, the Source.Name column is the first column, followed by all of the additional columns that are common to all of the Excel files.  

 

My goal is to have a measure that sums up the total of the potential for all of the Source.Name files.   I could then add them to chart (by month), or put visuals like what I have below side by side.    In other words, to replace the big card visual below, using my current method I would have to duplicate the measure below, but change the source file referenced in the measure to the 1-6-20 Excel file.    I know there must be a smarter way.  

 

I made a simple measure but it only works if I select a particular source.name file, and only if I filter by the relevant month.     

 

My data model is below.   

 

SumX of Source.Name = CALCULATE(SUMX(Forecasts,Forecasts[Potential]),FILTER(Forecasts,Forecasts[Source.Name]="Top FCST to review 1-27-20.xlsx"))

 

expected.png

 

data model.png

8 REPLIES 8
parry2k
Super User
Super User

@Anonymous so if I understood correctly all the excel files are appended together with the file name as source column, correct? So what is not working? What you are trying to achieve?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k  Sorry, I don't think I answered your "what's not working" question.  

 

As I mentioned, right now that formula only works if, after the = sign, the formula refernces a particular file  (in source.name column).   

I'm trying to get it to where I don't have to specifically mention a particular file, but can acheive getting to a particular result by using filters/slicers.     So, to use the two tables in my original post.    I would put two card visuals on the table, with the same measure, and just filter the measure differently for each visual.  

Anonymous
Not applicable

@parry2k   -   Yes, that is correct.  

 

The goal is to compare beginning of the month forecast, with end of the month forecast, and see what changed.   And do this for each month, over the year.   

@Anonymous so file name tells which is for beginning of the month and which is the last date of the month or there is a date column that identifies start and end of the month



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Sorry, one more thing! There is a date column (tied to the date table) that is called Est.Close Estimated Close represents the date the salesperson thinks the deal will ship.    (no idea why we called it that...but that is what it means).   

 

So, we are comparing all of the opportunities at the beginning of the month, with the opportunities on the report at the end of the month, based on estimated close dates relative to that month.  

@Anonymous This is what I would recommend, slap sample data of few months in excel sheet (few rows) and also expected result and share it through one drive/google drive. Remove any sensitive information before sharing it.

 

You solved one part to append all files together, after that it is a matter of putting DAX measures together and for that need to see the data.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thanks @parry2k 

 

I have redacted my original file.    Hopefully the enclosed file will help you, help me!  😁

 

https://drive.google.com/open?id=1rqoTuUJMz32yXOzUasZnFRSY9npNRFIN 

Anonymous
Not applicable

@parry2k   I do not have a start or end date column  (although that was suggested in the tutorial I got the idea from).   

 

https://spr.com/power-bi-quick-tips-dealing-with-snapshot-data/

 

To use my examples, the 1-6-20 is the date that someone extracted data from our CRM system  (i.e. the beginning of the month).  

 

The 1-27-20 data was the last time they ran the same report at the end of January.     The goal is to compare what changed between those two files (relative to only the month of January...because each of those files actually has forecast data for the entire year...but I'm only interested in what each forecast snapshot says relative to the month the report was ran.    Unfortunately we don't have a good automated way to do this.   

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.