Forum Discussion

saydu338's avatar
saydu338
New Member
6 years ago

retrieve data based on field

CLIENT IDPARENT IDCLIENT COUNTRYPARENT COUNTRY (New Column)
12345678UKFR
5678nullFRnull
91015678ITAFR

 

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

Need Help

4 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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's avatar
      saydu338
      New Member
      6 years ago

      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's avatar
        edhans
        Community Champion
        6 years ago

        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"
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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