Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSuper 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. 😁
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@AVS218 You will need to do this in Power Query, not DAX.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |