Forum Discussion

omarelmb123's avatar
omarelmb123
Icon for Helper I rankHelper I
3 years ago

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
John31/12/202166Medium
John31/08/202277High
David30/06/20228Low
Spenoza30/09/202370High
David31/12/202242Low

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
John31/12/202166Medium  
John31/08/202277HighHigh 
David30/06/20228Low  
Spenoza30/09/202370High  
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

 

Need Help

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
    jennratten
    Icon for Super User rankSuper User
    3 years ago

    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