Dax into power query

Power Query
1

Hi there,

I am calculating a column using the above-mentioned logic in DAX:

Last 24 hr Raw =
SUMX(
FILTER(‘Fact Table’ , ‘Fact Table’[EpochHH] >= EARLIER( ‘Fact Table’[EpochHH] ) - 47 && ‘Fact Table’[EpochHH] <= EARLIER( ‘Fact Table’[EpochHH] ) ), ‘Fact Table’[30 Mins Raw ])

I want to convert the above DAX formula into power query language. I am not sure is there any alternate for earlier and sumx functions in the query language. Could anyone help me in converting above formula into query language?

2

Yes there are, variables and List.Sum
If you need assistance please provide a sample PBIX with separate data file (like an xlsx for example)

3

@Melissa, Thanks. I have attached .pibx file. I want to create above-mentioned column in Table 2. EpochHH.pbix (2.1 MB) sample_data.xlsx (3.6 MB)

4

Hi @leo_89,

Had to do some testing to speed this up, so I hope performance is acceptable when scheduled in the Service. If not and you can’t retrieve this value from the source, you’re back to a calculated column…

Here’s what I’ve done. Set up.

// Sort EpochHH Ascending AND buffer the 30 Mins Raw list into memory
Buffered30mRaw = List.Buffer(Table.Sort( ChType[[EpochHH], [#"30 Mins Raw"]],{{"EpochHH", Order.Ascending}})[#"30 Mins Raw"]),
// Get the first value from the EpochHH column 
FirstNum = List.Min( List.Buffer(ChType[EpochHH])),

.
A list is an ordered sequence of values using that property we can now leverage the EpochHH from the current row to retrieve values from the Buffered30mRaw list.

Add24HRaw = Table.AddColumn( ChType, "Last 24 hr Raw", each 
  try
  List.Sum(
    List.FirstN(
      List.Skip( Buffered30mRaw, ([EpochHH] - FirstNum - 47) ), 
      48
    )
  )
  otherwise null, 
  type number
)

.
Note the supplied source file didn’t match the columns in the query.
Here’s a sample PBIX with new source CSV.
Table 2.csv (9.0 MB)
EpochHH.pbix (1.8 MB)

I hope this is helpful.

5

@Melissa, Sorry it did not work. It throws an error. I changed the source file to Table2.csv but still throwing error

6

Need a bit more to go on here…
Can you post your updated file?

7

@Melissa , here is the updated file. EpochHH (1).pbix (1.6 MB)

8

Removed the rename step that was raising an error.

EpochHH (1).pbix (1.6 MB)

9

@Melissa, I implemented the Add24HRaw into my actual pibx file and I got the following error:

image
image1885×963 66.4 KB

10

Hi @leo_89,

Your Table reference, the first argument, is pointing to the query (itself).

You need to replace that with #“Changed Type6” you will also need to check and possibly update that for the other steps you copied over.

I hope this is helpful.

11

@Melissa, Sorry to bother you again. I changed the Table reference to “Cahnged Type 6”. Now the error is gone. However, the new calculated column has all null values :unamused: :frowning_face:

12

Did you also change that Table reference in the other steps you’ve copied to #“Changed Type6” ?
And do the data types for each column in the sample match you production file?

image
image728×124 4.97 KB

13

@Melissa,

image
image1296×308 16.9 KB

Result:

image
image348×839 11.7 KB

14

Hi @leo_89,

This, highlighted in red, doesn’t look good to me - its a text value because there is no # in front but does have quotes. Like mentioned in my previous response this should be #“Changed Type5” as well

image
image1250×82 8.81 KB

15

@Melissa, Thanks. It worked.