Conditional Merge in Power Query

fireball.1 0
Viewed 41

I have 2 data sources FirstAPI and SecondAPI:

FirstAPI

appId policyTypeName policyTypeId
abcd Global null
pqrs null 1
pqws Global null

SecondAPI

policyTypeName policyTypeId
Americas 1
Global 2

The values are all one-to-one mapping.

RESULT : I am trying to add a column to FirstAPI so that it can look like the following:

appId policyTypeName policyTypeId newCol
abcd Global null Global
pqrs null 1 Americas
pqws Global null Global

I am trying to write a query in the advanced editor so that I can add newCol in FirstAPI. My attempt is to use something similar:

if [policyTypeName] = null 
then SecondAPI[policyTypeName] where SecondAPI[policyTypeId]=[policyTypeId]
else [policyTypeName]
powerquery powerbi-desktop powerbi
1 Answers

enter image description here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKTlFQ0lFyz8lPSswBsfJKc3KUYnWilQoKi4phAiDaECpaXoxNQywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [appId = _t, policyTypeName = _t, policyTypeId = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"appId", type text}, {"policyTypeName", type text}, {"policyTypeId", Int64.Type}}),
    Custom1 = Table.AddColumn(#"Changed Type", "Custom", (x)=> Table.SelectRows(SecondAPI, (y)=>  if x[policyTypeName] = y[policyTypeName] then true else if   x[policyTypeId] = y[policyTypeId] then true else false  )),
    #"Expanded Custom" = Table.ExpandTableColumn(Custom1, "Custom", {"policyTypeName"}, {"newCol"})
in
    #"Expanded Custom"
userjpYe10
Related