Retain only the first match value and return blank for the rest

[Keith011]

Hi there

I have 2 tables. I have merged them together in Power Query Editor. However, when i expand the table it will give me the table below (Original Outcome).

What I want is only to keep the values of the first ocurrence and return blank for the rest of the repeating ocurrence. (refer to below My Desired Table outcome)

Right table

country	date	Unit	Target
MY	202301	MC	1000
MY	202302	MC	5000
MY	202303	MC	10000

Left table

country	date	Unit
MY	202301	MC
MY	202301	MC
MY	202302	MC
MY	202302	MC
MY	202303	MC
MY	202303	MC

Original Outcome

country	date	Unit	Target
MY	202301	MC	1000
MY	202301	MC	1000
MY	202302	MC	5000
MY	202302	MC	5000
MY	202303	MC	10000
MY	202303	MC	10000

My desired table outcome

country	date	Unit	Target
MY	202301	MC	1000
MY	202301	MC	null
MY	202302	MC	5000
MY	202302	MC	null
MY	202303	MC	10000
MY	202303	MC	null

[v-jingzhang · Accepted solution]

Hi @Keith011 

Here is the whole M code of my Left table query. You can download the pbix file at bottom to see detailed transforamtion steps. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8o1U0lEyMjAyNjAEMnydlWJ1CAoaES1ojE8wFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country = _t, date = _t, Unit = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"country", type text}, {"date", Int64.Type}, {"Unit", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"country", "date", "Unit"}, {{"All Data", each Table.AddIndexColumn(_, "index", 0, 1)}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"All Data"}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Removed Other Columns", "All Data", {"country", "date", "Unit", "index"}, {"country", "date", "Unit", "index"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded All Data", {"country", "date", "Unit"}, #"Right table", {"country", "date", "Unit"}, "Right table", JoinKind.LeftOuter),
    #"Expanded Right table" = Table.ExpandTableColumn(#"Merged Queries", "Right table", {"Target"}, {"Target"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Right table", each [Target], each if [index] > 0 then null else [Target], Replacer.ReplaceValue, {"Target"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"index"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Keith011]

is there any way i could achieve this?