Forum Discussion

lasse0hlsen's avatar
lasse0hlsen
Frequent Visitor
3 years ago

Selecting value from different row

Hey folks,   I have a dataset where projects are listed twice if the project has both a "Committed amount" and a "Provided amount" and these two funding amounts each have a different Rio marker com...
Need Help
jennratten's avatar
jennratten
Super User
3 years ago

Hello - this will return the expected result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lGKiIwCksYGYABkAZEhFDsX5RcX6yaXlpRk5qUrxeqgawIpM4XoMsCpxdTE2AgkExUZQaQ9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Project name" = _t, #"Committed amount" = _t, #"Provided amount" = _t, #"KLM (Rio marker)" = _t, #"KLA (Rio marker)" = _t, #"Type of support" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Project name", type text}, {"Committed amount", Int64.Type}, {"Provided amount", Int64.Type}, {"KLM (Rio marker)", Int64.Type}, {"KLA (Rio marker)", Int64.Type}, {"Type of support", type text}}),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each let 
        varProjects = List.Select ( #"Changed Type"[Project ID], (x) => x = [Project ID] ), 
        varValues = 
            Table.SelectRows ( 
                #"Changed Type", 
                (x) => 
                    x[Project ID] = [Project ID] and 
                    x[Committed amount] <> null 
                    and x[Provided amount] = null 
            ) [Type of support]
        in 
        if List.Count ( varProjects ) > 1 then varValues{0} else null
    )
in
    #"Added Custom"

 

lasse0hlsen's avatar
lasse0hlsen
Frequent Visitor
3 years ago

Hey there,

 

When I try to apply the above M code to my actual dataset (which has approx. 11,000 rows and 120 columns), for some reason Excel spits out an error for the newly added "Custom" column that says: Expression.Error: There were too many elements in the enumeration to complete the operation. Details: List.

I wonder why this error occurs 🤔. Could it be that Excel is trying to squeeze multiple values into the same cell (for the duplicate project entries, which i would like to have displayed in one row)? Can someone help me with this? Unfortunately, I cannot share the original dataset, as it contains confidential information. I made sure that the "Project ID" column does not contain any nulls. I also double-checked that there are no projects that have values in both the "Committed amount" and "Provided amount" columns.

 

Many thanks in advance!

 

Best,

Lasse

  • jennratten's avatar
    jennratten
    Super User
    3 years ago

    Hello - the first part of the script (Source and Changed Type steps) is just creating a sample table to work with.

    This is how the Changed Type step breaks down (I've added comments to the script):

        #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each let 
// Variable with a list of all project Ids matching that of the current row.   
     varProjects = List.Select ( #"Changed Type"[Project ID], (x) => x = [Project ID] ), 
// Variable with a list of all Type of Support values where the project ID matches that of the current row, Committed amount <> null and Provided amount = null.
        varValues = 
            Table.SelectRows ( 
                #"Changed Type", 
                (x) => 
                    x[Project ID] = [Project ID] and 
                    x[Committed amount] <> null 
                    and x[Provided amount] = null 
            ) [Type of support]
        in 
// If varProjects has more than one element in the list then return the first Type of Support match, otherwise return null. 
       if List.Count ( varProjects ) > 1 then varValues{0} else null
    )

    Try running this script instead to try and identify the problematic records.  The error message you are receiving basically means it is looking for a 1:1 match and it is finding more than one.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lGKiIwCksYGYABkAZEhFDsX5RcX6yaXlpRk5qUrxeqgawIpM4XoMsCpxdTE2AgkExUZQaQ9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Project name" = _t, #"Committed amount" = _t, #"Provided amount" = _t, #"KLM (Rio marker)" = _t, #"KLA (Rio marker)" = _t, #"Type of support" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Project name", type text}, {"Committed amount", Int64.Type}, {"Provided amount", Int64.Type}, {"KLM (Rio marker)", Int64.Type}, {"KLA (Rio marker)", Int64.Type}, {"Type of support", type text}}),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each let 
        varProjects = List.Select ( #"Changed Type"[Project ID], (x) => x = [Project ID] ), 
        varValues = 
            Table.SelectRows ( 
                #"Changed Type", 
                (x) => 
                    x[Project ID] = [Project ID] and 
                    x[Committed amount] <> null 
                    and x[Provided amount] = null 
            ) [Type of support]
        in 
        try if List.Count ( varProjects ) > 1 then varValues{0} else null otherwise "Error"
    )
in
    #"Added Custom"