Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Trying to figure out the M code equivalent to add column,"final test result", in power query,
- In column "s/n", look for equivalent s/n
- In column "station", look for equivalent station,
- In column "test count", look for test count,
output
- if "test count" is not highest, output =null
- if "test count" is higest, output= test result.
Below is a sample data (actual data >1m rows)
Solved! Go to Solution.
You can use Group By:
let Source = Table1, #"Grouped Rows" = Table.Group(Source, {"Srudent", "subject"}, {{"LastAttempt", each List.Max([Attempts]), type number}, {"AllData", each _, type table}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Attempts", "test result"}, {"Attempts", "test result"}), #"Added Custom" = Table.AddColumn(#"Expanded AllData", "Final Test Result", each if [Attempts] = [LastAttempt] then [test result] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"LastAttempt"}) in #"Removed Columns"
Hi @vincentakatoh,
I did not understand the comparison value, it follows Power M:
Table.AddColumn(last Steps , "Name Column", each if [Test Count] > "?" then [test result] else "null")
Hi @EvertonPessoa,
Thanks. Will try another example as below:
I can get the "First test results quickly, by using below
"First Test Result", each if [Attempts] = 1 then [test result] else null
Nonetheless, struggling to add column "Final test result"
The intend is to get the final (last attempt) test result for each "student" and "subject". There is no limit on number of attempts for each subject.
You can use Group By:
let Source = Table1, #"Grouped Rows" = Table.Group(Source, {"Srudent", "subject"}, {{"LastAttempt", each List.Max([Attempts]), type number}, {"AllData", each _, type table}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Attempts", "test result"}, {"Attempts", "test result"}), #"Added Custom" = Table.AddColumn(#"Expanded AllData", "Final Test Result", each if [Attempts] = [LastAttempt] then [test result] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"LastAttempt"}) in #"Removed Columns"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |