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
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"