Forum Discussion

Rachel_123's avatar
Rachel_123
Icon for Helper I rankHelper I
3 years ago
Solved

Please help converting DAX to M

Hi, I've been struggling with a problem for awhile and finally found a workaround but to implement the solution I need to create a table in Power query in M, can anyone help me please translate the f...
Need Help
  • Rachel_123's avatar
    Rachel_123
    3 years ago

    somebody gave me a script that worked

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Project", "Expense Type", "Date"}, {
    {"Actuals", each List.Sum(Table.SelectRows(_, each [Versions] = "Actuals")[Value])},
    {"Current Budget", each List.Sum(Table.SelectRows(_, each [Versions] = "Current Budget")[Value])},
    {"Current Forecast", each List.Sum(Table.SelectRows(_, each [Versions] = "Current Fcst")[Value])}
    })
in #"Grouped Rows"


     

JoZu's avatar
JoZu
Frequent Visitor
3 years ago

Hi Rachel_123 

 

Try the following:

let
    Source = #"Portfolio Reporting",
    #"Grouped Rows" = Table.Group(Source, {"Project", "Expense Type", "Date"}, {{"Actuals", each List.Sum(List.Select([Value], each [Versions] = "Actuals")), type nullable number}, {"Current Budget", each List.Sum(List.Select([Value], each [Versions] = "Current Budget")), type nullable number}, {"Current Forecast", each List.Sum(List.Select([Value], each [Versions] = "Current Fcst")), type nullable number}})
in
    #"Grouped Rows"

 

I dont have any data to try it, sorry. But let me know if it works 🙂

Best regards

  • Rachel_123's avatar
    Rachel_123
    Icon for Helper I rankHelper I
    3 years ago

    Thank you so much, so this works for all the columns except the ones we are summarizing (Actuals, Current budget and Current forecast) I get error on those, something about cannot apply field access