Forum Discussion
tomshaw83
4 years agoHelper I
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...
- 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"
AlexisOlson
4 years agoSuper User
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
4 years agoHelper I
Thank you