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

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

    • BeaBF's avatar
      BeaBF
      Super User
      1 year ago

      Matt_JEM in sorteddata step, you're still using Source step, instead of dbo_QotMaster step. Try with my code:

      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
      RenamedColumns = Table.RenameColumns(dbo_QotMaster, {{"Quote", "Quote"}, {"QuoteVersion", "QuoteVersion"}}),

      // Step 3: Sort data by Quote (ascending) and QuoteVersion (descending)
      SortedData = Table.Sort(RenamedColumns, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}}),

      // Step 4: 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

        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

  • Matt_JEM's avatar
    Matt_JEM
    Helper I
    1 year ago

    Good Day BeaBF

     

    The following Code does sort the data correct.

    Table.Sort(dbo_QotMaster, {{"Quote", Order.Ascending}, {"QuoteVersion", Order.Descending}})

     

    But the following code still delete the Row where the version is lowest and delte the row where the status is higher

     

    Please help.

     

    Regards Matt