Forum Discussion

jcastr02's avatar
jcastr02
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Merge Queries - Bring Latest Value only

I am trying to merge queries - brining in the tempoary support table into the Store List.  I am looking to only get the Latest Support Date vs brining all items over.  SEe below , I'd only bring over 2 values.  Any ideas how I can achieve this?  Thank you.

Temporary Support  Store List
Store #Support Start Date Store #Support Start Date
111879/18/2023 111879/18/2023
111879/11/2023 5012/8/2023
111879/5/2023   
111878/28/2023   
111872/15/2023   
111878/24/2023   
501/1/2023   
5012/1/2023   
5012/8/2023   
502/1/2023   
Need Help

9 Replies

  • CoreyP's avatar
    CoreyP
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Is there a requirement these tables actually be merged in Power Query? Or are you able to accomplish this with DAX measures? 

      • CoreyP's avatar
        CoreyP
        Icon for Solution Sage rankSolution Sage
        2 years ago

        You should just be able to create a max measure. Last Date = MAX( 'Temporary Support'[Support Start Date] ).  Then in your visual pull in Store # and this measure.

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi jcastr02 

    Create a Black Table in Power Query
    Open Advance Editor
    Paste below code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bczBDQAgCEPRXTiTYFEjzmLcfw25EZXry2/XIgA2iGkKTLRopc2XItP+o6+TAxXkbQvtxQmCVzQjeyiifQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store #" = _t, #"Support Start Date" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Support Start Date", type date}}, "en-US"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Store #"}, {{"Count", each List.Max([Support Start Date]), type nullable date}})
in
    #"Grouped Rows"

     

    If solved your requirement, please mark this answer as SOLUTION.
    If this comment helps you, appreciate your KUDOS