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

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=

 

 

  • 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