Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Keith011
Helper III
Helper III

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

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

countrydateUnitTarget
MY202301MC1000
MY202302MC5000
MY202303MC10000

 

Left table

countrydateUnit
MY202301MC
MY202301MC
MY202302MC
MY202302MC
MY202303MC
MY202303MC

 

Original Outcome

countrydateUnitTarget
MY202301MC1000
MY202301MC1000
MY202302MC5000
MY202302MC5000
MY202303MC10000
MY202303MC10000

 

My desired table outcome

countrydateUnitTarget
MY202301MC1000
MY202301MCnull
MY202302MC5000
MY202302MCnull
MY202303MC10000
MY202303MCnull
1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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"

vjingzhang_0-1685069309956.png

 

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

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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"

vjingzhang_0-1685069309956.png

 

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

Keith011
Helper III
Helper III

is there any way i could achieve this?

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.