Quickly select the source file from SharePoint folder for a given report

[Nelson_Marquez]

Hi community!

I have a report that I need to update several times with different cases or sensitivities, all coming from an Excel file with the same structure (same spreadsheets, same tables, same columns, etc). 

My Excel source files are stored in SharePoint. After long digging in the web, I found out that in order to do so, I need to go to each of my queries, under 'Applied Steps', and change the M code in the Advanced editor with the new source info. That works fine, but is a bit cumbersome.

After talking with a colleague of mine, he told me he's using some sort of workaround with custom columns in the import, manipulating file info, so that he has a visualization where he selects what he wants to see. However he's using OneDrive, which changes everything. Does anyone know how to to so? Help would be much appreciated.

Thanks and regards

Nelson

[AllisonKennedy]

@Nelson_Marquez

Are you able to provide a bit more detail or screenshots of what you have and what you want to improve? If I'm guessing correctly, it sounds like you have data from multiple spreadsheets as different tables in Power BI, but want to use Get Data > From Folder or similar method to combine all the tables into one table, with a column for 'source' so you can filter in the report for exactly what you want to see?

You can also use 'Data Source Settings' to update the source step for multiple tables in one go if they all have the same source, but this doesn't work if you need to make a big change (like switching from Excel to web source for example)

[Nelson_Marquez]

Hi @AllisonKennedy 

The situation is the following:

1) There is a single Excel file, with multiple spreadsheets that feed my PowerBI report;

2) The source Excel file is stored in a SharePoint folder 

3) In my PowerBI report I have multiple queries (Sorry, you'll see next a Portuguese screenshot, but I guess it's enough to have the big picture)

4) Whenever I want to change the source data file I need to go to each one of my queries and change the M code in the Advanced editor;

5) I'm quite new with DAX and M code, but I've been wondering whether it's possible to create kind of a variable where I somehow save the file name and use it embedded into the source M code to call the file I want at a selection of a given option in a filter in visualization.

What I believe a colleague of mine told me is that he kind of added some custom column at the import stage in order to combine the files with their respective name, calling that same name, hence being able to see the data he selects.

[AllisonKennedy · Accepted solution]

I'm not sure if this is what you mean, but something like I've done in this post:

Use parameters to combine data

Stage A: Because the data comes from the SharePoint folder, you must already have the Parameter table, so when you type the SharePoint site name, filter the path of the folder you want, but leave all Excel files there. If you don't already have a column with the file path, you may need to perform a merge or something similar: I can't remember exactly what SharePoint data looks like, so you may need to share a screenshot of your Power Query if you need help at this stage.

Stage B - I think you've already done most of stage B - you've created your sample files, now you just need to create the parameter.

Stage C: Convert existing queries to functions and reference the file path as a parameter.

Stages D and E should make more sense if you've come this far.