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. 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

 

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

15 Replies

  • 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

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

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

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

      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