Forum Discussion
Solved
Power Query - get data from another column based on date
Hello all, I have a business problem that requires a solution similar to (Solved: Power Query - Get data from another table if condi... - Microsoft Fabric Community) question however I haven't b...
Hi, powerplatp0
You can try the following methods.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLNTMzNBNJm+ob6RgZGJkCmc35uQU5qSapSrE60khGSGiOEGs+8ZKgqBbAyY6BYSGJuQSKQNjTUN8QwDKIMJOJflJOYl5IPZJnrGxqA1JmCNDu5gFWYoqgw1TeGKUBxlRmaKkOsqsxBbk/NScovLcpLBbIt9Y1MQQqNUDygFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Location = _t, Date = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Location", type text}, {"Date", type date}, {"Status", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Location"}, {{"Data", each _}}), Custom1 = Table.TransformColumns(#"Grouped Rows",{"Data", each Table.FirstN(_,1)}), #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"ID", "Status"}, {"Data.ID", "Data.Status"}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Expanded Data", {"Data.ID"}, "Expanded Data", JoinKind.LeftOuter), #"Expanded Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Data", {"Data.Status"}, {"Data.Status"}) in #"Expanded Expanded Data"Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello powerplatp0
please check if this accomodate your need.
create new calculated column with following DAX.
New =
var _MaxDate =
MAXX(
FILTER(
'Table',
'Table'[Location]=EARLIER('Table'[Location])
),
'Table'[Date]
)
Return
IF(
'Table'[Date]=_MaxDate,
'Table'[Status],
"Null"
)
Hope this will help.
Thank you.