create new table from existing table with distinct values and no blank

[dwhittaker1]

I would like to create a new table from the existing table below. I would like the table to return distinct salesorder values and the acompanying Max date. I would also like to have the blank value removed from the table.

Salesorder	Max Date	Item
	12/31/2023	shoe
100	1/1/2024	box
101	1/3/2024	bed
101	1/4/2024	spring
102	1/7/2024	toy

The new table would look like below

Salesorder	Max Date
100	1/1/2024
101	1/4/2024
102	1/7/2024

[ThxAlot · Accepted solution]

Simple enough,

[kpost]

I have attached a solution in Power Query:

let
Source = Excel.Workbook(File.Contents("C:\Users\kpost\Downloads\source_table.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Salesorder", Int64.Type}, {"Max Date", type date}, {"Item", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Salesorder] <> null and [Salesorder] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Item"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Salesorder"}, {{"Max_Date", each List.Max([Max Date]), type nullable date}})
in
#"Grouped Rows"

If that doesn't make sense to you I have also attached the power BI file where I made these transformations for you to copy them.  

Here's the actual .pbix file where I solved the problem, for you to download and look at it.

https://files.catbox.moe/v226dm.pbix

Basically the steps are:

-Copy the Table

-Filter the first column to remove the blanks

-Delete the third column

-"Group by" using the following settings in Power Query:

 

//Mediocre Power BI Advice,  but it's free//

[Greg_Deckler]

@dwhittaker1 Maybe:

New Table = 
  VAR __Table = DISTINCT(SELECTCOLUMNS(FILTER('Table'[SalesOrder]), [Salesorder] <> BLANK()),"__Salesorder", [Salesorder]))
  VAR __Table1 = 
    ADDCOLUMNS(
      __Table,
      "__MaxDate",
          VAR __SO = [__Salesorder]
          VAR __Date = MAXX(FILTER('Table',[Salesorder] = __SO), [Max Date])
      RETURN
        __Date
    )
  VAR __Result = SELECTCOLUMNS( __Table1, "Salesorder",[__Salesorder],"Max Date", [__MaxDate])
RETURN
  __Result

[dwhittaker1]

@Greg_Deckler  thanks for the the help. I am getting an incorrect syntax error. my data set is in direct query. im not sure if that changes any thing.

[kpost]

FYI to @dwhittaker1 

Either @Greg_Deckler or my solution will work, but his is using DAX and mine is using Power Query.  It kind of depends on how you want to optimize your Power BI report as to which one you should choose.  But for smaller data sets it will not matter which one you choose, practically speaking.

Good luck.