Query Editor: Alternative to "Group By"

[vincentakatoh]

Hi, 

Is there any alternative to the "Group by" function. Below is my sample data:

1) Objective is to add column, "Final Test Result" (in blue). 

2) I can add the column, Final Test Result, using the "Group by" function. But it works only small sample data. My actual data has 18 columns and >500k rows (per csv file). After adding 4 csv files, the Query editor fails. Think calculation is too much for Power BI. 

3) As such, will like to know if there is any other alternative methold to add the column "Final Test Result"

[v-qiuyu-msft]

Hi @vincentakatoh,

You can try to use DAX to return the expected column:

Final test result = var t=CALCULATE(MAX('Table2'[Attempts]),ALLEXCEPT(Table2,Table2[Student],'Table2'[subject]))
return

IF('Table2'[Attempts]=t, LOOKUPVALUE('Table2'[test result],'Table2'[Student],'Table2'[Student],'Table2'[subject],'Table2'[subject],'Table2'[Attempts],t),BLANK())

Best Regards,
Qiuyun Yu

[vincentakatoh]

hi @v-qiuyu-msft,

Awesome.

Is there a way to add as a Column (m code?) instead of a Measure (dax)? Will need to use the column, FinalTestResult for other purpose. 

[v-qiuyu-msft]

Hi @vincentakatoh,

Based on the sample in attached test0630.pbix in my previous post. Create a blank query, paste the Power Query below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUfJNLMkAUoZA7JaYmQOjYnXQVRghVGCRNcYra4JdNjg5MzUvORW/9QhFOFyAUAByREBicTFMQXBiTk4lfj+iKUG3Ak0a3ZcwaVSPQJ0ApkCqnFJBznTNS8/JLEZzB1Z5I2RvxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Student = _t, subject = _t, Attempts = _t, #"test result" = _t, #"First test result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"subject", type text}, {"Attempts", Int64.Type}, {"test result", type text}, {"First test result", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Student", "subject"}, {{"MaxAttemptsEachGroup", each List.Max([Attempts]), type number}, {"all", each _, type table}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Attempts", "test result", "First test result"}, {"all.Attempts", "all.test result", "all.First test result"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded all", "FinalTestResult", each if [MaxAttemptsEachGroup] = [all.Attempts] then [all.test result] else null )
in
    #"Added Conditional Column"

For more information, see attched test0630(update).pbix file.

Best Regards,
Qiuyun Yu

[RobertSlattery]

You can just add a calculated column

output = Table.AddColumn(#"my table", "Final Test Result", each if [test result] = "Pass" or [First test result] = "Pass" then "Pass" else null)