Dynamically bringing in new data sources

[Andrea_Jess]

Hi All, 

I'm having a bit of difficulty executing the below. 

My team stores data in different tables within a SQL database each financial year and i need to create a Power BI report that will allow the team to view historical data as well as current financial year data. For example, FY20 data is stored in Sales_FY20 table, FY21 data is stored in Sales_FY21 table and so forth.

What i'm now struggling with is understanding if there is a way where Power BI can dynamically bring in the new table each year (i.e. once we reach june 30 2022, is there a way for power bi to dynamically pull in the table for for Sales_FY23)? If we can guarantee that the naming convention will remains 'Sales_FY23', is there a way i can pull in the new data table at the end of FY22? 

I thought of parameters but now i'm not sure if that would work.

Any suggestions would be appreciated. 

Thanks in advance. 

[mahoneypat]

Starting with an Excel sheet is fine, although FYI that you could have started with Source = {"Sales_A", "Sales_B", "Sales_C"} for future reference.  From there you need to add a custom column that uses the same code as the Source line of either your Sales_A or Sales_B query.  Where you see "... Sales_A", you need to update it to be "..." & [Column1].  If you share that M code, a more specific suggestion can be provided.

Pat

[Andrea_Jess]

Hi Pat, 

Thanks for your help. 

Here is the PBIX with the M code and the source data. 

I tried to replicate your instructions but i think i'm inputting the [Column1] in the incorrect place. 

Thanks in advance

[mahoneypat]

Yes.  You can start with a list like = {20..23}, convert that to a table, make it a text column, and then concatenate it into your database query (adapt the code from the Source step of one of your current queries for each year).  If the table structure is identical for each row, you can then expand that column of "Table"s to combine all the data.

If needed, you could also dynamically generate your initial list from the current year.

Pat

[Andrea_Jess]

Hi Pat,

Thanks for your help! Appreciate it .

I'm not well versed with writing m query so my apologies on this question. I'm using Excel workbooks to test this out (i'm assuming logic would be the same, i would just point to my database in the actual scenario). 

I have created a list with three excel workbooks (Sales_A and Sales_B are existing files which have been brought into PBI, and Sales_C is the file that should be brought in at the end of this fiscal year (hypothetically)). 

In terms of concatenating the source code via query editor, are you able to suggest how i may do that/where i should be focusing on? 

thanks so much in advance for your help.