Declare an integer that can be added to and assign value as column

[JollyRoger01]

I am trying to do this in Excel Power Query, however I have heard the code is just the same in Power BI, which has a more active community forum.

I have 6 different columns. I want to create a seventh column containing a score for the row based on whether certain columns are NOT null. The point of this is so I can choose rows based on how complete their data is. Having data in some columns is more important than others.

The first column is worth 60 points, second column 50 points etc. In Python, I would calculate the score like:

score = 0

if Column 1 <> null:

    score = score + 60

if Column 2 <> null

    score = score + 50

if Column 3 <> null

    score = score + 40

if Column 4 <> null

    score = score + 30

if Column 5 <> null

    score = score + 20

if Column 6 <> null

    score = score + 10

I want to know how to write this in the Advanced Editor.

[PhilipTreacy]

Hi @JollyRoger01 

Download sample PBIX with this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJBRbE60UqJQAYMwyiQOJoQiIIKwxGyWpgpiUhGI/QiKy4EMjBxbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",null,Replacer.ReplaceValue,{"Column2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","",null,Replacer.ReplaceValue,{"Column3"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","",null,Replacer.ReplaceValue,{"Column4"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","",null,Replacer.ReplaceValue,{"Column5"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","",null,Replacer.ReplaceValue,{"Column6"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value5", "Index", 0, 1, Int64.Type),
    

    #"Added Custom" = Table.AddColumn(#"Added Index", "Score", each 

        List.Sum(
    
        List.Generate(

            () => [i = 60, x = 0, y = List.RemoveLastN(Record.ToList(#"Added Index"{[Index]}),1), z = 0],

            each [z] <= List.Count([y]),
            
            each [x = if [y]{[z]} <> null then [i] else 0, i = [i] - 10, z = [z] + 1, y = [y] ],

            each [x]
            
        )
                
        )
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Which gives this result in the Score Column

Regards

Phil

[JollyRoger01]

Hi Phil, thanks for this. The output is exactly what I am looking for, however I am having some trouble figuring out where to start the code from.

I have changed the names of my columns to Column1, Column2 etc. to match your code. I have then opened Advanced Editor and remove the text under "in" and placed a comma at the end of the line above it. Then I have pasted everything from the start of "let _t" onwards, as I figured I didn't need the source again. But it is giving me an error.

[PhilipTreacy · Accepted solution]

Hi @JollyRoger01 

Try this Excel Workbook instead

I've created a table (in green) on a worksheet and then created a query to process the data.  The result is in the grey table on the same sheet as the source table.

You could now take this code and modify the source step to load your own table - but bear in mind that the code is designed to process 6 columns as shown in your sample data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    

    #"Added Custom" = Table.AddColumn(#"Added Index", "Score", each 

        List.Sum(
    
        List.Generate(

            () => [i = 60, x = 0, y = List.RemoveLastN(Record.ToList(#"Added Index"{[Index]}),1), z = 0],

            each [z] <= List.Count([y]),
            
            each [x = if [y]{[z]} <> null then [i] else 0, i = [i] - 10, z = [z] + 1, y = [y] ],

            each [x]
            
        )
                
        )
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Regards

Phil

[amitchandak]

@JollyRoger01 , Try a new column like

score new =
Switch(true(),
isblank([Column 1]), [score] + 60,
isblank([Column 2]), [score] + 50,
isblank([Column 3]), [score] + 40,
isblank([Column 4]), [score] + 30,
isblank([Column 5]), [score] + 20,
isblank([Column 6]), [score] + 60
)