GroupBy M Language - with IF for each row to get Value

[ccernat]

Hello, guys!

I have the table bellow and I'm proper stucked, as I don't know how to proceed. It would be wonderful a hint to get me going.

First, I need to switch all the Month names in the Attribute column to their corresponding numbers. After, I need to use Table.GroupBy for the first 7 columns and to return me the first non zero value from Value column for each row where Atrribute ( month number ) < Month. 

Is it possible such abomination? 🙂 I need a small path to follow, I can expand from there. Thank you very much!

[v-juanli-msft · Accepted solution]

Hi @ccernat 

Create such M query

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\4\4.24\4.24.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"year", Int64.Type}, {"month", Int64.Type}, {"CheieAnLuna", type date}, {"site", type text}, {"Cod Client", Int64.Type}, {"Cod Contract", Int64.Type}, {"Cod Spatiu", Int64.Type}, {"Attribute", type text}, {"Value", Int64.Type}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Attribute] = "January" then 1 else if [Attribute] = "February" then 2 else if [Attribute] = "Mars" then 3 else if [Attribute] = "April" then 4 else if [Attribute] = "Mail" then 5 else if [Attribute] = "June" then 6 else if [Attribute] = "July" then 7 else if [Attribute] = "August" then 8 else if [Attribute] = "Sepember" then 9 else if [Attribute] = "October" then 10 else if [Attribute] = "November" then 11 else if [Attribute] = "December" then 12 else null),
    #"Grouped Rows" = Table.Group(#"Added Custom Column", {"year", "month", "CheieAnLuna", "site", "Cod Client", "Cod Contract", "Cod Spatiu"}, {{"TOREur", each List.First(Table.SelectRows(_, each [month]>[Custom])[Value]), type table [year=number, month=number, CheieAnLuna=date, site=text, Cod Client=number, Cod Contract=number, Cod Spatiu=number, Attribute=text, Value=number, Custom=number, Custom.1=number]}})
in
    #"Grouped Rows"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ccernat]

Came up with this:

= Table.Group(#"Added Custom", {"Cod Client", "Year", "Month", "Cod Contract", "CheieAnLuna", "Cod Spatiu"}, {{"TOREur", if List.AllTrue([Value]>0, [Attribute]<[Month]) then each List.First([Value]) else [Value]), type number}})

But it says a comma is expected. What am I doing wrong?