Add a dynamic number of columns to a data table.

[Anonymous]

I have been given a requirement for a PowerBI report to add 1-11 dynamic columns, one for each month to the end
of the current financial year, from the current date. The beginning of the financial year starts in April.
I have a main table and have added calculated columns which include 'number of remainingMonths' and 'future value for months'.
So, depending on the number of remainingMonths, columns should be added to the main table for each month,
for example if date is 25th December 2024 then remainingMonths=3 and there should be 3 columns added for Jan,Feb and Mar 2025.
They will be named Jan,Feb,Mar, though Month1,Month2,Month3 would also be acceptable.
Each of these columns should contain the same value which is the 'future value for months' already calculated on the table. It will be the same for all rows because it works off the Current date, not a date on the row.
If the current date were 25th May 2025 then there would be 10 columns added to the table, each with the same value.
I cannot see a way to do this in PowerBI, to me it seems easiser doing it on the database first.
The source database is SQLServer so it is Power BI desktop being used.
Is this feasible? Can a dynamic number of columns be added to PowerBI data?

[Anonymous · Accepted solution]

Thanks for all your replies, but I have not been able to implement them by adding the month columns onto the data table. The solutions seem to add them onto the date table. The data table does not have a month number or todays date to try and link with. The difficulty is ADDING 1 or more dynamic columns to the data table - I just cannot see how it can be done.

I did think off adding 12 columns to the table, one for each month, and then filtering, but this still leaves the problem of displaying data, because those 12 columns would only hold the month number to filter on.

[Anonymous]

Hi johnt75 ,thanks for the quick reply, I'll add more.

Hi @Anonymous ,

Regarding your question, I am using “GETDATE()” function in Sql Server to get the current date and using Power BI to connect to the table.

Click on Advanced Editor in Power Query and enter the following M code:

let
    Source = Sql.Database("VM0", "123"),
    dbo_DateTable = Source{[Schema="dbo",Item="DateTable"]}[Data],
    CurrentDate =   DateTime.LocalNow(),
    CurrentMonth = Date.Month(CurrentDate),
    TargetMonth = 4,
    CurrentYear = Date.Year(CurrentDate),
    TargetYear = if CurrentMonth <= TargetMonth then CurrentYear else CurrentYear + 1,
    StartMonth = CurrentMonth,
    EndMonth = TargetMonth,
    MonthsList = if StartMonth <= EndMonth then List.Transform({StartMonth+1..EndMonth-1}, each if _ <= 12 then _ else _ - 12)
    else
    List.Transform({StartMonth+1..12} & {1..EndMonth-1}, each if _ <= 12 then _ else _ - 12),
    Result = Text.Combine(List.Transform(MonthsList, each Text.From(_)), ", "),
    AddColumn = Table.AddColumn(dbo_DateTable, "Custom", each Result)
in
   AddColumn

Final cut by comma

Final output

Best Regards

[Anonymous · Accepted solution]

Thanks for all your replies, but I have not been able to implement them by adding the month columns onto the data table. The solutions seem to add them onto the date table. The data table does not have a month number or todays date to try and link with. The difficulty is ADDING 1 or more dynamic columns to the data table - I just cannot see how it can be done.

I did think off adding 12 columns to the table, one for each month, and then filtering, but this still leaves the problem of displaying data, because those 12 columns would only hold the month number to filter on.

[Anonymous · Accepted solution]

This has not been solved, instead SQL has been used to set up a future 'calendar' for all possible years/months and then this is used as a drillthrough

[Anonymous]

Hi @Anonymous ,

Create a new column “number of remainingMonths” in Sql Server using Sql statement. Calculated columns are not available in Power Query. I think what you want can only be done using Power Query, dax can't do it.

M code

let
                     //columnname  Replace the column names with your own        
    NewRow = #table({"Type", "Group", "Amount","number of remainingMonths"}, 
                    //value   Consistent with your column names
                   {{"Type", "Group", "Amount","number of remainingMonths"}}),
    //Generate consecutive months based on the current month, separated by commas
    CurrentDate = DateTime.LocalNow(),
    CurrentMonth = Date.Month(CurrentDate),
    //Fiscal year end month
    TargetMonth = 4,
    CurrentYear = Date.Year(CurrentDate),
    TargetYear = if CurrentMonth <= TargetMonth then CurrentYear else CurrentYear + 1,
    StartMonth = CurrentMonth,
    EndMonth = TargetMonth,
    MonthsList = if StartMonth <= EndMonth then List.Transform({StartMonth+1..EndMonth-1}, each if _ <= 12 then _ else _ - 12)
    else
    List.Transform({StartMonth+1..12} & {1..EndMonth-1}, each if _ <= 12 then _ else _ - 12),
    Result = Text.Combine(List.Transform(MonthsList, each Text.From(_)), ", "),
    AddColumn = Table.AddColumn(NewRow, "Month", each Result),
    #"Split Column by Delimiter" = Table.SplitColumn(AddColumn, "Month", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Month.1", "Month.2", "Month.3", "Month.4", "Month.5", "Month.6", "Month.7", "Month.8", "Month.9", "Month.10", "Month.11", "Month.12"}),
    RemoveNullColumns = Table.SelectColumns(#"Split Column by Delimiter", List.Select(Table.ColumnNames(#"Split Column by Delimiter"),each List.NonNullCount(Table.Column(#"Split Column by Delimiter", _)) > 0)),
    #"Promoted Headers" = Table.PromoteHeaders(RemoveNullColumns, [PromoteAllScalars=true]),
    #"Appended Query" = Table.Combine({#"Promoted Headers", FactTable}),
    RenameColumnNames = Table.TransformColumnNames( #"Appended Query", each Text.Trim(_)),
    ColumnsExist1 = Table.HasColumns(RenameColumnNames, "1"),
    Result1 = if ColumnsExist1 then Table.ReplaceValue(RenameColumnNames,null,each [number of remainingMonths],Replacer.ReplaceValue,{"1"}) else RenameColumnNames,
    ColumnsExist2 = Table.HasColumns(RenameColumnNames, "2"),
    Result2 = if ColumnsExist2 then Table.ReplaceValue(Result1,null,each [number of remainingMonths],Replacer.ReplaceValue,{"2"}) else Result1,
    ColumnsExist3 = Table.HasColumns(RenameColumnNames, "3"),
    Result3 = if ColumnsExist3 then Table.ReplaceValue(Result2,null,each [number of remainingMonths],Replacer.ReplaceValue,{"3"}) else Result2,
    ColumnsExist4 = Table.HasColumns(RenameColumnNames, "4"),
    Result4 = if ColumnsExist4 then Table.ReplaceValue(Result3,null,each [number of remainingMonths],Replacer.ReplaceValue,{"4"}) else Result3,
    ColumnsExist5 = Table.HasColumns(RenameColumnNames, "5"),
    Result5 = if ColumnsExist5 then Table.ReplaceValue(Result4,null,each [number of remainingMonths],Replacer.ReplaceValue,{"5"}) else Result4,
    ColumnsExist6 = Table.HasColumns(RenameColumnNames, "6"),
    Result6 = if ColumnsExist6 then Table.ReplaceValue(Result5,null,each [number of remainingMonths],Replacer.ReplaceValue,{"6"}) else Result5,
    ColumnsExist7 = Table.HasColumns(RenameColumnNames, "7"),
    Result7 = if ColumnsExist7 then Table.ReplaceValue(Result6,null,each [number of remainingMonths],Replacer.ReplaceValue,{"7"}) else Result6,
    ColumnsExist8 = Table.HasColumns(RenameColumnNames, "8"),
    Result8 = if ColumnsExist8 then Table.ReplaceValue(Result7,null,each [number of remainingMonths],Replacer.ReplaceValue,{"8"}) else Result7,
    ColumnsExist9 = Table.HasColumns(RenameColumnNames, "9"),
    Result9 = if ColumnsExist9 then Table.ReplaceValue(Result8,null,each [number of remainingMonths],Replacer.ReplaceValue,{"9"}) else Result8,
    ColumnsExist10 = Table.HasColumns(RenameColumnNames, "10"),
    Result10 = if ColumnsExist10 then Table.ReplaceValue(Result9,null,each [number of remainingMonths],Replacer.ReplaceValue,{"10"}) else Result9,
    ColumnsExist11 = Table.HasColumns(RenameColumnNames, "11"),
    Result11 = if ColumnsExist11 then Table.ReplaceValue(Result10,null,each [number of remainingMonths],Replacer.ReplaceValue,{"11"}) else Result10,
    ColumnsExist12 = Table.HasColumns(RenameColumnNames, "12"),
    Result12 = if ColumnsExist12 then Table.ReplaceValue(Result11,null,each [number of remainingMonths],Replacer.ReplaceValue,{"12"}) else Result11

in
    Result12

1.Assuming this is the fact table I get from connecting to sql sever:(For convenience I used excel)

2.Create a Blank Query

3.Generates consecutive months, separated by commas, based on the fiscal year end month and the current month.

4.Cut into new columns based on commas and remove blank columns

5.Use the value of the first row as the column name and “Append” it to the fact table.

6.Replace the null value with the corresponding “number of remainingMonths” value.

Please check the attachment for details, replace the path of the excel file here with the path in your computer.

[johnt75]

I would suggest a different approach. Make sure that your date table has all dates at least up until the end of the current financial year, and add a column to the date table to flag when that date is within the current financial year. You could create a calculated column like

Is Current Financial Year =
VAR YearStart =
    SWITCH (
        TRUE (),
        MONTH ( 'Date'[Date] ) <= 3, YEAR ( 'Date'[Date] ) + 1,
        YEAR ( 'Date'[Date] )
    )
VAR YearEnd =
    SWITCH (
        TRUE (),
        MONTH ( 'Date'[Date] ) <= 3, YEAR ( 'Date'[Date] ),
        YEAR ( 'Date'[Date] ) - 1
    )
VAR DateStart =
    DATE ( YearStart, 4, 1 )
VAR DateEnd =
    DATE ( YearEnd, 3, 31 )
VAR Result = DateStart <= 'Date'[Date]
    && DateEnd >= 'Date'[Date]
RETURN
    Result

Place this as a filter on the visual where you want to show the months.

Create another calculated column on the date table like

In Current Month or Before =
'Date'[Date] <= EOMONTH ( TODAY (), 0 )

and finally create a measure to show your values

MyMeasure =
IF (
    SELECTEDVALUE ( 'Date'[In Current Month or Before] ),
    [Base Measure],
    [Future Value for Months]
)

Where [Base Measure] is whatever aggregation you need to show for actual values, and [Future Value for Months] is a measure which returns the same value all the time. It can be hard coded, calculated or stored in a disconnected table in your model.