Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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"
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.
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"
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.
Hi @Anonymous
Can you provide some sample data in a format which we can copy? And expected result as well.