Forum Discussion
lasse0hlsen
3 years agoFrequent Visitor
Selecting value from different row
Hey folks, I have a dataset where projects are listed twice if the project has both a "Committed amount" and a "Provided amount" and these two funding amounts each have a different Rio marker com...
jennratten
3 years agoSuper User
Hello - this will return the expected result.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lGKiIwCksYGYABkAZEhFDsX5RcX6yaXlpRk5qUrxeqgawIpM4XoMsCpxdTE2AgkExUZQaQ9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Project name" = _t, #"Committed amount" = _t, #"Provided amount" = _t, #"KLM (Rio marker)" = _t, #"KLA (Rio marker)" = _t, #"Type of support" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Project name", type text}, {"Committed amount", Int64.Type}, {"Provided amount", Int64.Type}, {"KLM (Rio marker)", Int64.Type}, {"KLA (Rio marker)", Int64.Type}, {"Type of support", type text}}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each let
varProjects = List.Select ( #"Changed Type"[Project ID], (x) => x = [Project ID] ),
varValues =
Table.SelectRows (
#"Changed Type",
(x) =>
x[Project ID] = [Project ID] and
x[Committed amount] <> null
and x[Provided amount] = null
) [Type of support]
in
if List.Count ( varProjects ) > 1 then varValues{0} else null
)
in
#"Added Custom"
lasse0hlsen
3 years agoFrequent Visitor
Dear jennratten,
many thanks for your help! This has brought me much closer to my goal. Could you briefly explain how this M code works or what it does with the data?
Best,
Lasse