Forum Discussion
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 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 |
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"
2 Replies
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"Thank you
