Forum Discussion

tomshaw83's avatar
tomshaw83
Helper I
4 years ago
Solved

Return values in Power Query based on condition

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 valu...
  • AlexisOlson's avatar
    4 years ago

    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"