Forum Discussion

Matt_JEM's avatar
Matt_JEM
Helper I
1 year ago
Solved

Delate rows based on Column Value

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. Som...
Need Help
  • BeaBF's avatar
    BeaBF
    1 year ago

    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

Matt_JEM's avatar
Matt_JEM
Helper I
1 year ago

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's avatar
BeaBF
Super User
1 year ago

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

  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

    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

  • Matt_JEM's avatar
    Matt_JEM
    Helper I
    1 year ago

    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's avatar
    BeaBF
    Super User
    1 year ago

    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's avatar
    Matt_JEM
    Helper I
    1 year ago

    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=