Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Maxifs - Power Query

Hello Guys  🙂

 

Please, I need your help regarding the use maxifs in the power query 

 

I need your help to translate this function into power query language:

 

=IF(MAXIFS($C$2:$C$7,$A$2:$A$7,A2)=C2,"yes","no")

 

Thanks 

1 ACCEPTED SOLUTION

Ok @Anonymous - it isn't a function per se, but a series of functions using Table.Group and Table.Max. The code is below. It turns this:

edhans_0-1614556644562.png

into this:

edhans_1-1614556665889.png

 

It does it by grouping by the contract and determing the max Evaluation Date, then comparing it to each date for a given contract, returning true or false. These are real true/false values (like Excel's TRUE() and FALSE()). 

 

I used my own data because you posted data in an image, which is great for expected results, but I cannot copy and paste it in. The links I provided earlier show you how to supply data to the forum in a usable format.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUTLUN9Q3MjAyQGbG6mCRM0bIVQIFjBByRjjkLIFME7iZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contract = _t, #"Visit Date" = _t, #"Evaluation Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Visit Date", type date}, {"Evaluation Date", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Contract"}, 
            {
                {
                    "AllRows", 
                    each 
                        let
                            varTable = _
                        in
                    Table.AddColumn(
                        _, 
                        "Max Date", 
                        each 
                            let
                                varDate = [Evaluation Date]
                            in
                            Table.Max(varTable, "Evaluation Date")[Evaluation Date] = varDate
                        ), type table [Contract=nullable text, Visit Date=nullable date, Evaluation Date=nullable date, Max Date = nullable date]}
            }
        ),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Visit Date", "Evaluation Date", "Max Date"}, {"Visit Date", "Evaluation Date", "Max Date"})
in
    #"Expanded AllRows"

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

There is no "translating" functions. Please provide data on what you are working with and what you expect. You can show how it works in Excel, but to just pass MAXIFS nested in an IF function isn't really showing what your end goal is.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hi Edhans,

 

I apologize for not providing a sufficient explanation, as I think the proposition that I made was understandable

 

No, issue 

 

I have a question you raised in the Excel community regarding the following:

 

I'd like to write a function in Excel in the last update column to define
Which row is the most recent in the evaluation (evaluation date Col), taking into account the contract number (x, Y)

 

For clarity in the contract X, the latest evaluation update is on 04/01/2021

 

The answer is yes or no

 

 

Ahmedb96_0-1614435333599.png

 

 

 

someone answered me: 

=IF(MAXIFS($C$2:$C$7,$A$2:$A$7,A2)=C2,"yes","no")

 

After that I tested this function in my excel file  .... it's work 

Ahmedb96_0-1614437480230.png

 

 

Now, I need to write this function in power query format 

 

Thanks

 

 

 

Ok @Anonymous - it isn't a function per se, but a series of functions using Table.Group and Table.Max. The code is below. It turns this:

edhans_0-1614556644562.png

into this:

edhans_1-1614556665889.png

 

It does it by grouping by the contract and determing the max Evaluation Date, then comparing it to each date for a given contract, returning true or false. These are real true/false values (like Excel's TRUE() and FALSE()). 

 

I used my own data because you posted data in an image, which is great for expected results, but I cannot copy and paste it in. The links I provided earlier show you how to supply data to the forum in a usable format.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUTLUN9Q3MjAyQGbG6mCRM0bIVQIFjBByRjjkLIFME7iZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contract = _t, #"Visit Date" = _t, #"Evaluation Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Visit Date", type date}, {"Evaluation Date", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Contract"}, 
            {
                {
                    "AllRows", 
                    each 
                        let
                            varTable = _
                        in
                    Table.AddColumn(
                        _, 
                        "Max Date", 
                        each 
                            let
                                varDate = [Evaluation Date]
                            in
                            Table.Max(varTable, "Evaluation Date")[Evaluation Date] = varDate
                        ), type table [Contract=nullable text, Visit Date=nullable date, Evaluation Date=nullable date, Max Date = nullable date]}
            }
        ),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Visit Date", "Evaluation Date", "Max Date"}, {"Visit Date", "Evaluation Date", "Max Date"})
in
    #"Expanded AllRows"

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

You should do this calculation in DAX as it is much easier there.  Here is DAX column expression that shows one way to do it.

 

IsMostRecent =
VAR vThisEval = Table[Evaluation Date]
VAR vLatestEval =
    CALCULATE (
        MAX ( Table[Evaluation Date] ),
        ALLEXCEPT (
            Table,
            Table[Contract]
        )
    )
RETURN
    IF (
        vThisEval = vLatestEval,
        "Y",
        "N"
    )

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors