Merge Queries - Bring Latest Value only

[jcastr02]

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
11187	9/18/2023		11187	9/18/2023
11187	9/11/2023		50	12/8/2023
11187	9/5/2023
11187	8/28/2023
11187	2/15/2023
11187	8/24/2023
50	1/1/2023
50	12/1/2023
50	12/8/2023
50	2/1/2023

[CoreyP · Accepted solution]

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]

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

[CoreyP]

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

[jcastr02]

@CoreyP it can be a DAX, yes.

[CoreyP · Accepted solution]

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.

[jcastr02]

@CoreyP  Sorry I tried the above measure - but since I'm using the Store list to merge into and there should only be 1 record for each in that list and in the temporary support the store # may be multiple, when I add the measure to the table visual, it creates duplicate rows for numerous stores.

[PijushRoy]

Hi @jcastr02 

Did you try POwer Query step I shared earlier? Is it solve your requirement? Or you are looking for DAX?

[jcastr02]

@PijushRoy Thank you - would you be able to share a sample Pbix file, please?

[PijushRoy · Accepted solution]

@jcastr02 

Find the report link - https://drive.google.com/file/d/1gSyTaN9qLUmzYMwul37Z4FSxmsqLLr67/view?usp=sharing

[jcastr02]

This worked Thank you!