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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.