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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Replaces value in table if row is present in other table

Hi i have Table 1 and Table 2. I want to replace values in table 1 if that value is null and that row is also present in Table 2 but i do not know how to accomplish this in power query

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

You can try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICssAMZyDDDMxyAbLMwSxXIMtYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, #"Table 2", {"Name"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"Value"}, {"Table 2.Value"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table 2",null,each [Table 2.Value],Replacer.ReplaceValue,{"Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Table 2.Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}})
in
    #"Changed Type1"

t1.pngnt1.png

Attached a sample file in the below, hopes it could help.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

You can try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICssAMZyDDDMxyAbLMwSxXIMtYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, #"Table 2", {"Name"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"Value"}, {"Table 2.Value"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table 2",null,each [Table 2.Value],Replacer.ReplaceValue,{"Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Table 2.Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}})
in
    #"Changed Type1"

t1.pngnt1.png

Attached a sample file in the below, hopes it could help.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Vera_33
Resident Rockstar
Resident Rockstar

Hi @Anonymous 

 

Can you provide some sample data in a format which we can copy? And expected result as well.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors