Forum Discussion

jay_rpj's avatar
jay_rpj
Frequent Visitor
6 years ago
Solved

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 Ta...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

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's avatar
    jay_rpj
    Frequent Visitor
    6 years ago

    Thanks for responding 

     

    • AnkitBI's avatar
      AnkitBI
      Solution Sage
      6 years ago

      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.

      • jay_rpj's avatar
        jay_rpj
        Frequent Visitor
        6 years ago

        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