Forum Discussion
Delate rows based on Column Value
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
ExpandedDatacan you paste a sample data on which work in parallel?
BBF
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
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 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
ExpandedDatacan you paste a sample data on which work in parallel?
BBF