retrieve data based on field

[saydu338]

CLIENT ID	PARENT ID	CLIENT COUNTRY	PARENT COUNTRY (New Column)
1234	5678	UK	FR
5678	null	FR	null
9101	5678	ITA	FR

Hello,

Hope that you can help out here, as I could not find answer online.

As you can see certain of my client ID have parent ID, I would like to create a new column as presented above that can find me the country location of the parent. I would like to do this by adding a custom column in power query.

hope the table is self explanatory,

Thank you

[v-juanli-msft]

Hi @saydu338 

Is any answer helpful?

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

To get a better performance, could you accept a calculated column/measure using DAX outside the power query?

Best Regards

Maggie

[edhans]

Try this code. It returns the following data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTI1M7cAUqHeSrE60TBeXmlODpByCwILWhoaGCJUeoY4KsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CLIENT ID" = _t, #"PARENT ID" = _t, #"CLIENT COUNTRY" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CLIENT ID", Int64.Type}, {"PARENT ID", Int64.Type}, {"CLIENT COUNTRY", type text}}),
    #"Added Parent Country" = 
        Table.AddColumn(
            #"Changed Type",
            "Parent Country",
            each
            let
                varParentID = [PARENT ID]
            in
                try Table.SelectRows(
                        #"Changed Type", 
                        each [CLIENT ID] = varParentID)[CLIENT COUNTRY]{0}
                otherwise null,
                type text
        )
        
in
    #"Added Parent Country"

 

[saydu338]

Thanks @edhans it works, however I can't load the data it takes too long because I have 250000 rows.

is there a way to make it slower ?

thanks again

[edhans]

Try this method @saydu338 - I joined the table with itself.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTI1M7cAUqHeSrE60TBeXmlODpByCwILWhoaGCJUeoY4KsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CLIENT ID" = _t, #"PARENT ID" = _t, #"CLIENT COUNTRY" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CLIENT ID", Int64.Type}, {"PARENT ID", Int64.Type}, {"CLIENT COUNTRY", type text}}),
    #"Self Join" = Table.NestedJoin(#"Changed Type", {"PARENT ID"}, #"Changed Type", {"CLIENT ID"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Self Join", "Changed Type", {"CLIENT COUNTRY"}, {"Parent Country"})
in
    #"Expanded Changed Type"