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

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

        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