Forum Discussion

coliveira03_'s avatar
coliveira03_
Frequent Visitor
3 years ago
Solved

Dax expression to Power Query M

Hi there,
 
I am new to power query M language, and having a bit of dificulty on finding a way to write the following dax on the power query as a new column 
I have tried many ways, and that is vital to make a filter on my query and get only the data I need
 
I do appreciete any help
 
-----------------------------------------------------------------------------------------------------
Status =
var index1 = CALCULATE(SUM(Folder[Hooley Index]),
    FILTER(Folder,Folder[FolderNumber]=EARLIER(Folder[FolderNumber])))
return
index1
------------------------------------------------------------------------------------------------------
Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi coliveira03_ ,
    this is easiest to be done through the UI in Power Query. You group on "FolderNumber" and add 2 aggregation fields: One with the sum of column "Hooley Index" and another that returns all fields from the partitions (Operation: "All rows"). You expand all these columns out afterwards.

    1) Check the column "FolderNumber" -> rightclick mouse and check "Group By"

    2) Click on advanced and do 2 aggregations:

    a) Sum on "Hooley Index" as "index1" and

    b) "All rows"- operation with any string in "New column name" that you like.

     

    3) Expand out the columms from the "All rows" aggregation

     

1 Reply

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi coliveira03_ ,
    this is easiest to be done through the UI in Power Query. You group on "FolderNumber" and add 2 aggregation fields: One with the sum of column "Hooley Index" and another that returns all fields from the partitions (Operation: "All rows"). You expand all these columns out afterwards.

    1) Check the column "FolderNumber" -> rightclick mouse and check "Group By"

    2) Click on advanced and do 2 aggregations:

    a) Sum on "Hooley Index" as "index1" and

    b) "All rows"- operation with any string in "New column name" that you like.

     

    3) Expand out the columms from the "All rows" aggregation