Forum Discussion

saydu338's avatar
saydu338
New Member
6 years ago

retrieve data based on field

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...
Need Help
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"