Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have the following DAX formula which is working fine in my PBI-report but now I want to create a custom column within a dataflow and I have to use Power Query language. Can someone convert this DAX-formula to Power Query language so that it will work identical?!
You can achieve similar functionality in Power Query by creating a custom column. However, please note that Power Query doesn't support variables like DAX, so you'll need to work with nested functions instead. Here's the equivalent Power Query code:
```m
let
AktuellesDatum = [Datum],
LetzterUnfallTag = List.Max(
Table.SelectRows(#"PEL Unfallkreuz", each [PELA.Unfallfreie Tage] = 0 and [Datum] <= AktuellesDatum)[Datum]),
ErsterUnfallfreierTag = List.Min(Table.SelectRows(#"PEL Unfallkreuz", each [PELA.Unfallfreie Tage] > 0)[Datum]),
DaysDifference =
if LetzterUnfallTag = null then
Duration.Days(ErsterUnfallfreierTag - AktuellesDatum)
else
Duration.Days(LetzterUnfallTag - AktuellesDatum)
in
DaysDifference
```
This Power Query code calculates the number of days between the current date (`AktuellesDatum`) and either the last accident date (`LetzterUnfallTag`) or the first accident-free date (`ErsterUnfallfreierTag`). The result is then returned as the custom column value `DaysDifference`.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Thank you for the reply. Unfortunately I receive an error message saying
Expression.Error: During the evaluation a circular reference was detected.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
182 | |
86 | |
67 | |
62 | |
53 |