Delate rows based on Column Value

[Matt_JEM]

Good day All.

I need help with the following please.

The data below is a direct import from our SQL server. I am want to create a master quote file with a unique quote number for each quote. Sometimes a quote get revised and the QuoteVersion number is increased. 

I want to keep the Quote data with the highest Quote version Number, all the other rows with the same quote number but with a lower quote version numbers must be deleted. How do I do this in Power Query Editor.

EG. When looking at the picture below,  Row 1 must be deleted but Row 2 must be kept and Row 7 must be deleted But Row 8 must be kept.

I thank you in advance for your assistance.

Regards

Matt

[Matt_JEM]

Please see the query below.

let
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo",Item="QotMaster"]}[Data]
in
dbo_QotMaster

[Matt_JEM]

Hello BeaBF

I have implement the following code. It is not deleting the Row where the Quote version is lower that the next line with a higher quote version.

I wwould appreciate your assistance.

= let
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo",Item="QotMaster"]}[Data],
SortedData = Table.Sort(Source, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}}),
KeepLatestVersion = Table.Distinct(SortedData, {"Quote"})
in
dbo_QotMaster

Kind Regards 

Matt

[dufoq3]

Hi @Matt_JEM, check this:

Before

After

Replace word GroupKind.Local with GroupKind.Global in code if your [Quote] column is not sorted in the table, but keep it if it is. Do not sort it as a separate step - it is not necessary.

let
    Source = Table.FromColumns({{"0001", "0001", "0002", "0003", "0004", "0004"}, {0,1,0,0,1,0}}, {"Quote", "QuoteVersion"}),
    FilteredMaxQuoteVersion = Table.Combine(Table.Group(Source, {"Quote"}, {{"T", each Table.MaxN(_, {"QuoteVersion"}, 1), type table, GroupKind.Local}})[T])
in
    FilteredMaxQuoteVersion

[BeaBF]

@Matt_JEM Hi! can you provide your power query code or the data? Basically these are the steps that u have to do in your power query:

let
// Step 1: Load the data from the source (Replace with your actual data source)
Source = YourDataSource,

// Step 2: Sort the data by Quote (ascending) and QuoteVersion (descending)
SortedData = Table.Sort(Source, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}}),

// Step 3: Remove duplicates, keeping only the first (highest version per Quote)
KeepLatestVersion = Table.Distinct(SortedData, {"Quote"})

in
KeepLatestVersion

BBF

[Matt_JEM]

Good Day BeaBF.

I have changed the quote to the following, but then gets the following error.

let
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo",Item="QotMaster"]}[Data],
#"SortedData" = Table.Sort(Source, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}}),
#"KeepLatestVersion" = Table.Distinct(SortedData, {"Quote"})

in
KeepLatestVersion

ERROR Message:

Expression.Error: The specified sort criteria is invalid.
Details:
[List]

I am new to PowerBi and Power Query Editor. I would appreciate your help.

Thanks in advance 

Matt

[BeaBF]

@Matt_JEM Try with:

let
// Step 1: Connect to SQL Database and Load Table
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo", Item="QotMaster"]}[Data],

// Step 2: Sort data by Quote (ascending) and QuoteVersion (descending)
SortedData = Table.Sort(dbo_QotMaster, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}}),

// Step 3: Remove duplicates, keeping only the latest version per Quote
KeepLatestVersion = Table.Distinct(SortedData, {"Quote"})

in
KeepLatestVersion

BBF

[Matt_JEM]

Good Day BeaBF

The following Code does sort the data correct.

Table.Sort(dbo_QotMaster, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}})

But the following code still delete the Row where the version is lowest and delte the row where the status is higher

Please help.

Regards Matt

[Matt_JEM]

Good day BeaBF.

I have tried that but get an error. Please see code and error warning below.

let
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo",Item="QotMaster"]}[Data],
#"SortedData" = Table.Sort(Source, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}}),
#"KeepLatestVersion" = Table.Distinct(SortedData, {"Quote"})

in
KeepLatestVersion

ERROR Message:

Expression.Error: The specified sort criteria is invalid.
Details:
[List]

I thank you in advance for your assistance.

Regards

Matt

[BeaBF]

@Matt_JEM in sorteddata step, you're still using Source step, instead of dbo_QotMaster step. Try with my code:

let
// Step 1: Connect to SQL Database and Load Table
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo", Item="QotMaster"]}[Data],

// Step 2: Ensure column names are correct
RenamedColumns = Table.RenameColumns(dbo_QotMaster, {{"Quote", "Quote"}, {"QuoteVersion", "QuoteVersion"}}),

// Step 3: Sort data by Quote (ascending) and QuoteVersion (descending)
SortedData = Table.Sort(RenamedColumns, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}}),

// Step 4: Remove duplicates, keeping only the latest version per Quote
KeepLatestVersion = Table.Distinct(SortedData, {"Quote"})

in
KeepLatestVersion

BBF

[Matt_JEM]

Hi BeaBF.

I have implemented the code as per your instrections. Please see below.

Data is sorted correctly but the Row with the latest version is still dleted and the row with the earliest version is kept. Version 1 delted and version 0 is kept.

let
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo",Item="QotMaster"]}[Data],
#"RenamedColumns" = Table.RenameColumns(dbo_QotMaster, {{"Quote", "Quote"}, {"QuoteVersion", "QuoteVersion"}}),
#"SortedData" = Table.Sort(RenamedColumns, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}}),
#"KeepLatestVersion" = Table.Distinct(SortedData, {"Quote"})
in
KeepLatestVersion

[BeaBF]

@Matt_JEM Instead of using Table.Distinct(), we should group the data by "Quote" and then keep only the row with the maximum "QuoteVersion".

let
// Step 1: Connect to SQL Database and Load Table
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo", Item="QotMaster"]}[Data],

// Step 2: Ensure column names are correct (modify if needed)
RenamedColumns = Table.RenameColumns(dbo_QotMaster, {{"Quote", "Quote"}, {"QuoteVersion", "QuoteVersion"}}),

// Step 3: Group by Quote and keep the row with the MAX QuoteVersion
GroupedData = Table.Group(
RenamedColumns,
{"Quote"},
{{"AllData", each Table.Max(_, "QuoteVersion")}}
),

// Step 4: Expand the grouped data to get the latest version row for each Quote
ExpandedData = Table.ExpandRecordColumn(GroupedData, "AllData", Table.ColumnNames(RenamedColumns))

in
ExpandedData

BBF

[Matt_JEM]

Good day @BeaBF and @dufoq3 

I have implemented the code, please see below. And get the following error

Expression.Error: The field 'Quote' already exists in the record.
Details:
Name=Quote
Value=

let
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo",Item="QotMaster"]}[Data],
#"RenamedColumns" = Table.RenameColumns(dbo_QotMaster, {{"Quote", "Quote"}, {"QuoteVersion", "QuoteVersion"}}),
#"GroupedData" = Table.Group(RenamedColumns,{"Quote"},{{"AllData", each Table.Max(_, "QuoteVersion")}}),
#"ExpandedData" = Table.ExpandRecordColumn(GroupedData, "AllData", Table.ColumnNames(RenamedColumns))
in
ExpandedData

[BeaBF]

@Matt_JEM Fixed code below:

let
// Step 1: Connect to SQL Database and Load Table
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo", Item="QotMaster"]}[Data],

// Step 2: Sort data by Quote (ascending) and QuoteVersion (descending)
SortedData = Table.Sort(dbo_QotMaster, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}}),

// Step 3: Group by Quote, keeping only the first row (which now has the highest QuoteVersion)
GroupedData = Table.Group(SortedData, "Quote", {{"AllData", each Table.FirstN(_, 1)}}),

// Step 4: Expand the grouped data to get the latest version row for each Quote
ExpandedData = Table.ExpandTableColumn(GroupedData, "AllData", Table.ColumnNames(dbo_QotMaster))

in
ExpandedData

BBF

[Matt_JEM]

Hello @BeaBF & @dufoq3 

Sorry but the code as below is not working. Keep getting the following error.

Expression.Error: The field 'Quote' already exists in the record.
Details:
Name=Quote
Value=

[BeaBF · Accepted solution]

@Matt_JEM Try with:

let
// Step 1: Connect to SQL Database and Load Table
Source = Sql.Databases("jt-sysprosqlsvr"),
SysproCompanyJEM = Source{[Name="SysproCompanyJEM"]}[Data],
dbo_QotMaster = SysproCompanyJEM{[Schema="dbo", Item="QotMaster"]}[Data],

// Step 2: Sort data by Quote (ascending) and QuoteVersion (descending) so the highest version appears first
SortedData = Table.Sort(dbo_QotMaster, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}}),

// Step 3: Group by Quote, keeping only the first row (which now has the highest QuoteVersion)
GroupedData = Table.Group(SortedData, {"Quote"}, {{"AllData", each Table.FirstN(_, 1), type table [Quote=nullable text, QuoteVersion=nullable number, QuoteStatus=nullable text]}}),

// Step 4: Expand the grouped table, but exclude the duplicated "Quote" column
ExpandedData = Table.ExpandTableColumn(GroupedData, "AllData", {"QuoteVersion", "QuoteStatus"})

in
ExpandedData

can you paste a sample data on which work in parallel?

BBF