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.

     

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.

v-frfei-msft's avatar
v-frfei-msft
Community Support
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.