topic Re: How to expand nested list within specific column? in DAX Commands and Tips

How to expand nested list within specific column?

AVS218 — Sun, 08 Aug 2021 16:57:55 GMT

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

Re: How to expand nested list within specific column?

Greg_Deckler — Mon, 09 Aug 2021 01:03:22 GMT

ImkeF mahoneypat edhans

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

Re: How to expand nested list within specific column?

edhans — Mon, 09 Aug 2021 01:24:13 GMT

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.

Re: How to expand nested list within specific column?

AVS218 — Mon, 09 Aug 2021 07:40:36 GMT

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

Re: How to expand nested list within specific column?

edhans — Mon, 09 Aug 2021 14:57:13 GMT

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