Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to move a formula from DAX to Trasnform data, but I am only getting errors, can anyone help me to convert it? This is my formula. What is the way to represent this in Query Editor? I don't have that much because is always an error, thanks
Original formula
I asked ChatGPT your question exactly.. I'm curious if it will work for you!
let
today = DateTime.LocalNow(),
closingWd = SEM_TRX_ALL[ClosingWd],
isTodayBeforeClosingWd = Date.Day(today) <= closingWd,
filteredTable = Table.SelectRows(
SEM_TRX_ALL,
each [akritiv__Status__c] <> "Open" and (
[a_RevisedDocumentCategory] = "Invoice" or [a_RevisedDocumentCategory] = "Credit note"
) and [a_UltimateDue] > today and [a_UltimateDue] <= Date.EndOfMonth(today)
),
topCurrentFuturePaid = if isTodayBeforeClosingWd then null else List.Sum(filteredTable[Group_Currency_Amount__c])
in
topCurrentFuturePaid
The M query language is a functional language that operates on data in tables. In this code, we define some variables at the beginning (today and closingWd) and then use them in subsequent steps to filter and transform the SEM_TRX_ALL table. We use Table.SelectRows to filter the table and then use List.Sum to calculate the sum of the Group_Currency_Amount__c column.
Note that the M query code assumes that SEM_TRX_ALL is a table in the current Power Query environment. You may need to adjust the code to match the name and schema of your actual data source.
Hi, thanks for the help! But is not working
User | Count |
---|---|
94 | |
86 | |
78 | |
69 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |