Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
omarelmb123
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
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

 

2 REPLIES 2
BA_Pete
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.

 

Pete



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

Proud to be a Datanaut!




jennratten
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.

jennratten_0-1694430094432.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors