Return values in Power Query based on condition

[tomshaw83]

Hi all,

I have a query for bringing through values in power query. I have a table which has values in column 1, and some values repeated in column 2 - the rest of column 2 is made up of null values. I would like a column 3 which returns the column 2 value, and the values from column 1 immediately preceding and following it. I have tried merging the table with itself, but find it difficult to generate column 3 - for which I think there may be a more elegant solution.

Column 1	Column 2	Column 3
6	null	6
8	8	8
9	null	9
5	null	null
3	null	null
3	null	3
4	4	4
1	null	1
3	null	null
6	null	6
7	7	7
2	null	2
2	null	null

[AlexisOlson · Accepted solution]

This isn't a very elegant solution but it's the first thing that I came up with:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlPSUVKK1YlWsgAyLMAsS5iQKYxhjMEwATJMwCxDDDm4keZAhjmYZQQTgjBiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", Int64.Type}, {"Column 2", Int64.Type}}),
    Boolean = List.Transform(#"Changed Type"[Column 2], each if _ <> null then 1 else 0),
    Combine = List.Transform(
                List.Zip(
                    {
                        Boolean,
                        List.RemoveFirstN(Boolean, 1) & {0},
                        {0} & List.RemoveLastN(Boolean, 1)
                    }
                ), each List.Max(_)
            ),
    AddToTable = Table.FromColumns(Table.ToColumns(#"Changed Type") & {Combine}, Table.ColumnNames(#"Changed Type") & {"Boolean"}),
    AddColumn3 = Table.FromRecords(Table.TransformRows(AddToTable, each _ & [#"Column 3" = if [Boolean] = 1 then [Column 1] else null])),
    #"Changed Type1" = Table.TransformColumnTypes(AddColumn3,{{"Column 1", Int64.Type}, {"Column 2", Int64.Type}, {"Boolean", Int64.Type}, {"Column 3", Int64.Type}})
in
    #"Changed Type1"

[tomshaw83]

Thank you