Forum Discussion
Load second table based on the column value in First Table
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 .
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 Source13.Then we can get the excepted result once we invoke the parameter.
5 Replies
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"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 ?
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.
