How to expand nested list within specific column?

[AVS218]

Hi,

I'm looking for expanding nested list in column “Start Year” of attached pbix file. Can someone help?

Any better way to achieve same result in effective way? This post is continuation of followup question to solution provided in Solved: Line and Histogram visualization of Avg. Price wit... - Microsoft Power BI Community

Sample Pbix file  attached

@Anonymous , @daxer-almighty , @Fowmy , @amitchandak, @Tanushree_Kapse, @mussaenda 

[edhans · Accepted solution]

@AVS218 - this seems to be a relatively simple if/then/else construct. Why are you embeddding everyting in a list? Just change the Start Year column to this:

if [Count] = 1 then if [Custom] = 1 then [Contract Start Date] else Date.AddYears([Contract Start Date],[Custom]-1) else [Contract Start Date]

If there is some reason you want to keep creating lists just to extract from them, then you could do this:

    #"Start Year (Nested)" = 
        Table.AddColumn(
            #"Changed Type1", 
            "Start Year", 
            each 
                if [Count] = 1 then 
                    {if [Custom] = 1 then {[Contract Start Date]} else {Date.AddYears([Contract Start Date],[Custom]-1)}}{0}{0}
                else {[Contract Start Date]}{0}
            )

But it is the same result, with more work. Don't use squiggly brackets unless you definitely need to create a list. Here, you definitely do no need to do that.

[AVS218]

Super thanks @edhans ...Sometimes brain stops working when you look at 100k line items 😊.. Thanks for reminding me this... Highly appreciate 

[edhans]

Glad to help @AVS218 - sometimes a fresh set of eyes is all you need. 😁

[Greg_Deckler]

@ImkeF @mahoneypat @edhans 

@AVS218 You will need to do this in Power Query, not DAX.