Forum Discussion

powerplatp0's avatar
powerplatp0
New Member
1 year ago
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 been able to implement after working on this all day. 

 

I have the following aggregated table:

IDLocationDateStatus
1Miami 06/01/2024Complete
2Miami02/01/2024Incomplete 
3Tampa11/11/2024Complete 
4Orlando 07/10/2025TBD
5Orlando05/03/2025Complete
6Orlando05/01/2025Complete
7Melbourne09/25/2022Incomplete 

 

The table is already sorted so that the first entry of row of a common Location is the latest Date. I want create a new column that returns the Status of the most recent Date for every Location. The other Date rows should be null. The result table should look like: 

 

IDLocationDateStatusNew
1Miami 06/01/2024Complete

Complete

2Miami02/01/2024Incomplete null
3Tampa11/11/2024Complete Complete
4Orlando 07/10/2025TBDTBD
5Orlando05/03/2025Complete

null

6Orlando05/01/2025Completenull
7Melbourne09/25/2022Incomplete Incomplete

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    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.