Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

adding column dynamically depending on date || Complex problem

Hello everyone, Today I have a complex problem

I want to create columns dynamically . I have sales table see the example below:

PersonDateSalesLevel of sales

I want to create EOY column whenever i have 31/12 and retrieve the level of sales value. the wanted output will be :

PersonDateSalesLevel of salesEOY 21EOY 22
David31/12/202242Low Low

Any idea how to achieve it using M Query ? im thinking of grouping data by Person and then evaluate Month Column "I can add it to the table" and then add column with level of sales value ? 

Thank you


Super User
Super User

Hi @omarelmb123 ,


While @jennratten has produced your requested output perfectly, I would argue that what you're asking for is not, in fact, dynamic at all as you're having to hardcode dates and column names for each year required. Additionally, you're creating a denormalised table structure that is not the most efficient for reporting and may cause you issues later when trying to calculate measures.


I would suggest adding a custom column like this instead:

LevelOfSales_EOY =
if Date.Month([Date]) = 12 and Date.Day([Date]) = 31 then [Level of sales]
else null

The year for which you are viewing this new column value would then be controlled using a related calendar table, as is best practice.


Once this method is set up, it will dynamically add new entries to the column as new data values are added to the fact table with no further coding required.



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Super User
Super User

Hello - this is how you can do it.  Note, in your description you say that the expected result should be a new column which is populated when the month and day from the date column is 12/31, however in the example of the expected result, for John, the new columns are blank for 12/31/2021 and populated for 8/31/2022.  This solution has John's row for 12/31/2021 populated and blank for 8/31/2022.


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUTI00jc21DcyMDIEcszMgIRvakpmaa5SrA5cjYEFVI0RkGNuDiQ8MtMzwCpcEssyU0BKzPSNDWBKLIDYJ78crCC4IDUvvyoRpMQSqsQYZIoBNlPgjgGZYmIEMyYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Date = _t, Sales = _t, #"Level of sales" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Date", type date}, {"Sales", Int64.Type}, {"Level of sales", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Level of Sales EOY 21", each if Date.ToText ( [Date], "M/dd/yyyy" ) = "12/31/2021" then [Level of sales] else null, type text),
    Custom1 = Table.AddColumn(#"Added Custom", "Level of Sales EOY 22", each if Date.ToText ( [Date], "M/dd/yyyy" ) = "12/31/2022" then [Level of sales] else null, type text)

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors