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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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