Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I would like to get the following output in PowerBI. I've read a different post with a solved solution but unfortunately I can't reproduce the solution without an error. I've asked a question about it in that forum post but unfortunately nobody answers it. Therefore I've opened this new post and I hope someone can help me with a working solution. Old post: Solved: Asset Life Cycle Calculation - Microsoft Power BI Community
I need PowerBI to create the following table. Every AssetNo has to be replaced after it's expected Life for a fixed amount of costs. The next step would be to integrate a index so the replacements cost wil be indexed every year.
The calculated tabled is based on the following dataset. For example every 5 year AssetNo A has to be replaced for a value of € 1.000,--.
AssetNo | endyear | life | Assetcost |
A | 2018 | 5 | € 1.000 |
B | 2019 | 3 | € 2.000 |
C | 2020 | 5 | € 1.500 |
D | 2017 | 6 | € 1.000 |
I've build the following function that will provide a list of replacement years for each last replacement year. This gives an error.
let
output = List.Generate(
()=>[firstValue],
each _<= [endyear], each _+[life],
each _
)
in
output
This function creates a list but comes withe an error when I expand the list to new rows.
When I expand to new rows I get the following error. "Expression.Error: We cannot apply field access to the type Number."
When I place a fixed value for endyear (2050) and life (5) then it works fine. I can live wit a fixed value but the value live comes from the dataset.
let
output = List.Generate(
()=>[firstValue],
each _<= 2050, each _+5,
each _
)
in
output
The output is with tunnecessary columns deleted is:
Does somebody know a solotion for this problem or a different method in PowerBI. Thanks in advance.
Damon
https://1drv.ms/u/s!AvqOdziejqaXluYnp2YAh2Bpk5SYqA?e=oALPaS
Solved! Go to Solution.
Hi , @Deem
I download your .pbix file , For your problem, Here are my answers:
(1)At present, the value you modified is correct, and you can return the correct table, where 2050 means that the year of the generation stops in 2050, and the deadline for the generation is not specified in your table, so you need to replace the value with 2050.
(2)For the [life] field , you can edit the M language in "Aangepaste kolom toegevoegd" step:
= Table.AddColumn(#"Type gewijzigd", "Aangepast", (x)=> List.Generate(()=>x[endyear]+x[life],(y)=> y<=2050 , (y)=>y+x[life] ) )
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, @Deem
I test it with your data, Here are the steps you can refer to :
(1)This is my test data:
(2)We can Add a custom colimn in Power BI Query Editor:
(x)=> List.Generate(()=>x[Last Replaced],(y)=>y<=Number.From(x[End of Year]) ,(y)=>y+Number.From(x[Expected Life]) )
(3)Then we can click "Expand to Mew Rows".
(3)Then we aplly the data to the Power BI Desktop and we can put the field in the visual and we will meet your need:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @v-yueyunzh-msft,
Thank for your quick response. It looks great. Unfortunately I get a new column with function. And I can't open your Pbix because is searching for a Excelfile.
I will attach my original dataset in the opneing post.
Hi, @Deem
You can try to delete the "each" in the enter box :
Then you can get the list .
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @v-yueyunzh-msft ,
Sorry but removing each doesn't work. At the bottom of my original post I've added a Onedrive downloadlink of my pbix . Can you pleas have a look at it. Thanks.
@Deem Here is a DAX solution. Basically Blowout! PBIX is attached below sig.
Blowout! =
VAR __Table =
GENERATE(
'Table',
VAR __MinYear = [endyear]
VAR __increment = [life]
RETURN GENERATESERIES(__MinYear,__MinYear + __increment * 3,__increment)
)
RETURN
__Table
HI @Greg_Deckler ,
I wil try this solution later this day on my big dataset. I've tried it and it works on the test dataset. I will let you know if it works fine. Thanks!
Hi @Greg_Deckler , your solutions works also fine. The only thing is that it generates much more new rows because of the incrementfactor then the solution of @v-yueyunzh-msft. So I have chosen the solution of @v-yueyunzh-msft for my case.
Many thanks for your quick replies!!💪💪
Hi , @Deem
I download your .pbix file , For your problem, Here are my answers:
(1)At present, the value you modified is correct, and you can return the correct table, where 2050 means that the year of the generation stops in 2050, and the deadline for the generation is not specified in your table, so you need to replace the value with 2050.
(2)For the [life] field , you can edit the M language in "Aangepaste kolom toegevoegd" step:
= Table.AddColumn(#"Type gewijzigd", "Aangepast", (x)=> List.Generate(()=>x[endyear]+x[life],(y)=> y<=2050 , (y)=>y+x[life] ) )
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |