DAX EARLIER() function in Power Query

0 votes

I have a table with lots of different dates in column DATE and a smaller number of letters in column LETTER. I now want the maximum date for each letter.

In DAX, I would use something like 

CALCULATE(MAX([Date]),FILTER(ALL(Table),[Letter]=EARLIER([Letter])).

Is there an equivalent to EARLIER in M/Power Query?

Oct 1 in Power BI by anonymous
• 4,630 points
50 views

1 answer to this question.

0 votes

Solutions in the code below. Notice that each uses "PreviousStep" as a basis, so these are separate solutions.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    PreviousStep = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Letter", type text}}),

    // 1. Add a column to the original table with the MaxDate for each letter
    //    "earlier"  is just the name of a function parameter; it could as well have been "x" or "MarcelBeug"
    AddedMaxDate = Table.AddColumn(PreviousStep, "MaxDate", (earlier) => List.Max(Table.SelectRows(PreviousStep, each [Letter] = earlier[Letter])[Date])),

    // 2. Group by letter and get the MaxDate for each letter
    GroupedOnLetter = Table.Group(PreviousStep, {"Letter"}, {{"MaxDate", each List.Max([Date]), type date}})
in
    GroupedOnLetter
answered Oct 1 by Gitika
• 41,360 points

Related Questions In Power BI

0 votes
1 answer

Dax query in power bi

Here is my table before I have ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,410 points
229 views
0 votes
1 answer

list reports with calculated percentage in Power BI using dax

Create a Measure called Total Revenue: Total Revenue = SUMX( ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,410 points
354 views
0 votes
1 answer

Power Query Web request results in “CR must be followed by LF” Error

What I think is, it might look ...READ MORE

answered Oct 29, 2018 in Power BI by Shubham
• 13,450 points
498 views
0 votes
1 answer

Select Columns from table instead of removing afterwards in Power Query

Try this. let db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data], Sales_vDimCustomer = ...READ MORE

answered Oct 31, 2018 in Power BI by Shubham
• 13,450 points
1,088 views
0 votes
1 answer
0 votes
1 answer

Understanding the DAX CALCULATE function

Using the CALCULATE function makes the DAX perform a context ...READ MORE

answered Sep 28 in Power BI by Gitika
• 41,360 points
34 views
0 votes
1 answer
0 votes
1 answer

DAX Userelationship function

 You need a way for the measure ...READ MORE

answered Oct 5 in Power BI by Gitika
• 41,360 points
19 views
0 votes
1 answer

Calculate GB Using DAX in Power BI

Hi, @Vnk You can go through this regarding ...READ MORE

answered Jun 30 in Power BI by Gitika
• 41,360 points
174 views
0 votes
1 answer

What is the difference between DAX and Power Query?

DAX stands for Data Analysis Expressions. DAX ...READ MORE

answered Sep 24 in Power BI by Gitika
• 41,360 points
19 views