adding column dynamically depending on date || Complex problem

[omarelmb123]

Hello everyone, Today I have a complex problem

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

Person	Date	Sales	Level of sales
John	31/12/2021	66	Medium
John	31/08/2022	77	High
David	30/06/2022	8	Low
Spenoza	30/09/2023	70	High
David	31/12/2022	42	Low

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

Person	Date	Sales	Level of sales	EOY 21	EOY 22
John	31/12/2021	66	Medium
John	31/08/2022	77	High	High
David	30/06/2022	8	Low
Spenoza	30/09/2023	70	High
David	31/12/2022	42	Low		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

[BA_Pete]

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.

Pete

[jennratten]

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.

let
    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)
in
    Custom1