Query Editor: Custom column, List.Max, formula

[vincentakatoh]

 Hi, 

Trying to add custom column (AttemptsMax) to show the maximum attempts per student and per subject.

Need help on the custom column formula. Guess i should be using the List.Max function. Also wanted to avoid using the "Group by" feature.

[v-jiascu-msft · Accepted solution]

Hi @vincentakatoh,

Would you like to add a calculated column in the table? I tested a formula, similar with the one below, as a calculated column in a table with 3 millions rows. It's finished in less than one minute. Maybe you can have a try.

MaxAttempts =
CALCULATE (
    MAX ( 'Table1'[attempts] ),
    FILTER (
        'Table1',
        'Table1'[Student] = EARLIER ( 'Table1'[Student] )
            && Table1[subject] = EARLIER ( 'Table1'[subject] )
    )
)

Best Regards!

Dale

[Anonymous]

gOOD Day, when i apply this in power Query its giving me an expression error the Nale CALCULATE wasnt recognised

[TomMartens]

Hey @Anonymous ,

you have to be aware that the accepted solution provided by @v-jiascu-msft is based on DAX, for this reason this approach can not be used inside Power Query. Power Query is based on M (a functional programming language), and the data model is using DAX.

Regards,

Tom

[vincentakatoh]

Hi @v-jiascu-msft, 

Awesome. Works in <1min on actual data (280mb). 

Nonetheless, can advise how to add the equivalent using Query Editor "Custom Column"(M language), instead of "Calculated Column"(DAX). Reason being, eventually will need add more columns and get visuals such as pareto. 

Really appreciate your reply. Spent days on w/o success. 

[vincentakatoh]

Hi @v-jiascu-msft,

Fyi, tried using the M equivalent (Advanced Editor) in Query Editor, but had same issue (using Group-by) as PBI hangs when more data is loaded. 

Truly appreciate your idea for using Calculated Column. For this specific purpose, Calculated Column (DAX) is more resource efficient than Custom Column/Advanced Editor (M). 

[TomMartens]

Hmm,

I do not actually understand why you want to avoid the GroupBy custom column funtion.

If I want to add an aggregated value e.g. MAX(Attempts) to a Group (Subset) of values without loosing the detail values I do the following:

the column "expansion" is just a "dummy" column that is not further used.

After this I just have to expand the table and select the Value column.

Maybe this helps even it involves the GroupBy Transform

[vincentakatoh]

Hi @TomMartens, 

Thanks. Used the "Groupby" but problem is Query Editor hangs or fails when i run using my actual data (>500k rows, 70mb, per day). 

As such, I'm hoping I can try to add a custom column with formula instead. 

[TomMartens]

Okay I understand the problem.

So here we have to create very efficiently "a list" from where List.Max has to determine the MAX value. This has to be done for each row again and again ...

Have you tried a somewhat brute force method (or does Power BI hangs here as well)

• create a table with the grouped (MAX) value and later on (in SQL Server this would be CTE)
• merge the aggregated table to the base table (in SQL Server this would be Join the BaseTable with the CTE)