Load second table based on the column value in First Table

[jay_rpj]

Hello - I got two tables getting data from cosmos DB . I want the second table to be loaded based on the column value from First table . Is there a way I could do this through power query ?

Eg Table 1

Order ID - Timestamp

Table 2

Order ID - Customer Name - Phone 

Load the Table 2 only for the records based on the order id on Table 1 . 

[v-frfei-msft]

Hi  @jay_rpj ,

We can merge the query based on the id columns to work on it.

Then merge table like this:

M code for your reference.

table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUaqoUIrViVYyQjCNEUwTKDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID", Int64.Type}, {"time", type text}})
in
    #"Changed Type"

Table2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4oqKCqVYnWglIyA7CYlvDGQnQ/hQERMgLwVJhSmQnQrig3lmQFYanGcOZKXDeRZAVgacZwlkZUJ4sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, name = _t, phone = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID", Int64.Type}, {"name", type text}, {"phone", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Order ID"}, Table, {"Order ID"}, "Table", JoinKind.RightOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Table"})
in
    #"Removed Columns"

[jay_rpj]

Thanks for responding v-frfei-msft . I wanted to load the 2nd table based on the value of order ids in the first table instead of merging the data later . That way , I am restricting the read units in cosmos in the first place .

To add clarity , Table 2 should be loaded with the values for order ids in table 1 - which is ( 1-4)  instead of loading the order ids from 1 - 9 and doing a merge . Any thoughts ?

[AnkitBI]

1) Create a Custom Function to take ID as paramater and extract data from Table 2 based on the ID.

2) Then use Invoke custom function to extract details of Table 2 based on each Row of Table1.

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[v-frfei-msft · Accepted solution]

Hi @jay_rpj ,

I make an example for your reference.

1.Import an excel file to desktop add a custom column based on id column then create the parameter in power query.

M code in the power query is like this for step1.

(para as text) as table =>
let
    Source = Excel.Workbook(File.Contents("D:\Case\20180810\New Microsoft Excel Worksheet.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "account name"}, {"Column2", "id"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([account name] = para)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each "'" &[id] &"'")
in
#"Added Custom"

2.Then we can add some steps in the Advanced editor.

(para as text) as table =>
let
    Source = Excel.Workbook(File.Contents("D:\Case\20180810\New Microsoft Excel Worksheet.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "account name"}, {"Column2", "id"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([account name] = para)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each "'" &[id] &"'"),
    keylist= Text.Combine(#"Added Custom"[Custom],","),
    select1="SELECT * FROM servername.databasename.dbo.tablename WHERE  id IN (" & keylist & ")",
    Source1 = Sql.Database("servername ", "databasename", [Query=select1])
in
    Source1

3.Then we can get the excepted result once we invoke the parameter.

[jay_rpj]

Is there a way to restrict the data into table 2 at the load stage itself .

let
Source = DocumentDB.Contents("https://xxxxxxxxxx.documents.azure.com:443/", "xxxx", "Order", [Query = "select TOP 1 c.orderid,c['data']['order']['user']['name']from c where c.source='ActiveOrder' AND c.orderid=$Table1.OrderID"]
),
in
Source