Search table

[Nitch]

I have a large schema where tables do not always have the most recent CLT_ID which makes it difficult to track orders. I am hoping to create a search table that would allow me to merge them together.

Here is what I have to work with.

Table A includes a list of all of the CLT_IDs and the sequence in which they were created

TABLE A
CLIENT_ID	SQN_NBR
456789	105
8763	104
65247	103
8158	102
8547	101
58475	100

Table B includes a list of previous CLT_IDs and what it was changed to.

TABLE B
PREV_CLIENT_ID	CURRENT_CLIENT_ID
8763	456789
65247	8763
8158	65247
8547	8158
58475	8547

Here is what I would like the results to look like. I have tried a number of ways but am no where near figuring it out.

RESULTS TABLE
CLIENT_ID	CURRENT_CLIENT_ID
456789	456789
8763	456789
65247	456789
8158	456789
8547	456789
58475	456789

I would like for it to work using Direct Query but would happy to just make it work at this point. 

Thanks in advance.

[AlienSx]

@Nitch from what I see : Table B is enough to get results table. It's parent-child chain of records.

let
    Source = your_table_B,
    types = Table.TransformColumnTypes(Source,{{"PREV_CLIENT_ID", type text}, {"CURRENT_CLIENT_ID", type text}}),
    // record of all pairs
    rec = Record.FromList(types[CURRENT_CLIENT_ID], types[PREV_CLIENT_ID]),
    // list of all IDs of level zero
    parents = List.Difference(types[PREV_CLIENT_ID], types[CURRENT_CLIENT_ID]),
    // function to compose a list of parents and childs
    find_child = (parent, pairs, result) => 
        [next_child = Record.FieldOrDefault(pairs, parent),
        next_step = if parent = null then result else @find_child(next_child, pairs, result & {parent})][next_step],
    // function usage with parents list
    family = List.Transform(parents, (x) => find_child(x, rec, {})),
    // compose a table of all parents and family members
    tbl = Table.FromList(
        family, 
        (x) => {List.Last(x), x}, 
        {"CURRENT_CLIENT_ID", "CLIENT_ID"}
    ),
    // expand family members into new rows
    xpand = Table.ExpandListColumn(tbl, "CLIENT_ID")
in
    xpand

[v-xinruzhu-msft · Accepted solution]

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.