Forum Discussion
Dynamic Table Name Based on Previous Month
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
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
Source