Selecting value from different row

[lasse0hlsen]

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 combination (see "KLM" and "KLA" columns). The following table shows such a project:

My goal is to modify the dataset so that for projects that have been listed twice, the second project entry (where the value in the "Committed amount" column is null and the "Provided amount" column contains a numerical value) in the "Type of support" column is assigned the value from the first project entry (where there is a numerical value in the "Committed amount" column). The result should look like this:

Can such an operation be done with the Power Query Editor without using DAX? I want to thank you in advance for your help and look forward to your suggestions!

Best,
Lasse

[jennratten]

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]

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]

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"

[lasse0hlsen]

Dear @jennratten,

many thanks for your help! This has brought me much closer to my goal. Could you briefly explain how this M code works or what it does with the data?

Best,

Lasse