Search table

Hi Nitch 

If you want to achieve this in power query, you need to change the mode from direct query to import, becasue the operation will be limited if you use the direct query mode. you can refer to the following sample in power query, it use the import mode.

create two blank queries and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcrBFQAhCEPBXnL2oksgWwuP/ttQ9PgnyYTCPwwYPfSjRsK5LA7dpUGTOv38At+hvZuyYEN71QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PREV_CLIENT_ID = _t, CURRENT_CLIENT_ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PREV_CLIENT_ID", Int64.Type}, {"CURRENT_CLIENT_ID", Int64.Type}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcmxDQAhDAPAXVxTEIhJfpYo+6/xkqE8XRWcJ/LDgE2iRyHjbNHFw+Uh79vGFNcl35rI9KA80f0D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CLIENT_ID = _t, SQN_NBR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CLIENT_ID", Int64.Type}, {"SQN_NBR", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"CLIENT_ID"}, Query1, {"PREV_CLIENT_ID"}, "Query2", JoinKind.LeftOuter),
    #"Expanded Query2" = Table.ExpandTableColumn(#"Merged Queries", "Query2", {"CURRENT_CLIENT_ID"}, {"CURRENT_CLIENT_ID"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Query2", "Custom", each let //Define your columns below
 c=[CLIENT_ID],p=[CURRENT_CLIENT_ID],mytable=#"Expanded Query2",pc="CURRENT_CLIENT_ID",cc="CLIENT_ID" 
  in
let mylist={c} & List.Generate(()=>[x=0,y=p,w=1],each [w] > 0,each [z=[y], 
x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,cc)=z),pc),y=x{0},w=List.Count(x)
],
each [y])
        in
Text.Combine(List.Reverse(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""})),"|")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"CURRENT_CLIENT_ID", "Custom.2"})
in
    #"Removed Columns"

Output

If you want to achieve it in direct query, you can consider to use calculated table instead of the operations in power query.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.