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
AVS218
Frequent Visitor

How to expand nested list within specific column?

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 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
AVS218
Frequent Visitor

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

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

@ImkeF @mahoneypat @edhans 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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