Forum Discussion
Dynamic Table Name Based on Previous Month
Hi All,
I have a requirement to dynamically pick a table based on the previous month and year.
For example:
Table naming convention: SAMPLE_<YEARMONTH>
If today’s date is March 27, 2026, the table to be used should be:
👉 SAMPLE_202602 (previous month)
Requirement:
I want this logic to work dynamically whenever the report refreshes, without manually changing the table name.
Question:
Is it possible to dynamically construct and use table names in:
Power BI (Power Query / M)?
Any suggestions or best practices would be really helpful.
Thanks
Yes, this can be done in Power Query at refresh time, but not in the sense of “renaming” a table.
What you are asking for is whether Power Query can dynamically determine which source table to read, based on the previous month. That is different from changing a table name in the model.
So if your source really contains separate physical tables like SAMPLE_202601, SAMPLE_202602, SAMPLE_202603, and so on, then in some connectors you can build the previous month as text in M and use it to reference the matching source table during refresh.
Conceptually, the logic is:
let
PrevMonth = Date.AddMonths(Date.From(DateTime.LocalNow()), -1),
TableName = "SAMPLE_" & Date.ToText(PrevMonth, "yyyyMM"),
Source = Sql.Database("ServerName", "DatabaseName"),
Data = Source{[Schema="dbo", Item=TableName]}[Data]
in
DataSo the idea is not that Power Query changes the table name, but that it dynamically selects which table to load when the dataset refreshes.
That said, I would also mention that this is usually not the best design. If all of these monthly tables have the same structure, the cleaner approach is normally to store the data in a single table with a date or YearMonth column and then filter the required period. That model is much easier to maintain and works better with standard Power BI features such as incremental refresh.
So the short answer is: yes, dynamic table selection may be possible in Power Query depending on the source, but if this monthly split is only being used as a workaround for volume or refresh concerns, a single fact table with a proper date column is usually the better long-term solution.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
Yes, this can be done in Power Query at refresh time, but not in the sense of “renaming” a table.
What you are asking for is whether Power Query can dynamically determine which source table to read, based on the previous month. That is different from changing a table name in the model.
So if your source really contains separate physical tables like SAMPLE_202601, SAMPLE_202602, SAMPLE_202603, and so on, then in some connectors you can build the previous month as text in M and use it to reference the matching source table during refresh.
Conceptually, the logic is:
let
PrevMonth = Date.AddMonths(Date.From(DateTime.LocalNow()), -1),
TableName = "SAMPLE_" & Date.ToText(PrevMonth, "yyyyMM"),
Source = Sql.Database("ServerName", "DatabaseName"),
Data = Source{[Schema="dbo", Item=TableName]}[Data]
in
DataSo the idea is not that Power Query changes the table name, but that it dynamically selects which table to load when the dataset refreshes.
That said, I would also mention that this is usually not the best design. If all of these monthly tables have the same structure, the cleaner approach is normally to store the data in a single table with a date or YearMonth column and then filter the required period. That model is much easier to maintain and works better with standard Power BI features such as incremental refresh.
So the short answer is: yes, dynamic table selection may be possible in Power Query depending on the source, but if this monthly split is only being used as a workaround for volume or refresh concerns, a single fact table with a proper date column is usually the better long-term solution.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Thank you for your time and the reply.
let
PrevMonth = Date.AddMonths(Date.From(DateTime.LocalNow()), -1),
TableName = "SAMPLE_" & Text.PadStart(Text.From(Date.Year(PrevMonth)), 4, "0") & Text.PadStart(Text.From(Date.Month(PrevMonth)), 2, "0"),
Source = Sql.Database("server", "database", [Query="SELECT * FROM " & TableName])
in
SourceHello,
You can try building the table name as text using DateTime.LocalNow(), then using something like Expression.Evaluate or navigating the source dynamically, but it depends a lot on your source (SQL vs files vs dataflows), and it can get fragile a more stable approach is to handle it at the source, like creating a view in SQL that always points to the latest (or previous month) table, then Power BI just connects to that fixed name
Best regards,
Daniele
